package org.example.aitest.utils.gen;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.example.aitest.dto.EHL;
import org.example.aitest.dto.TableColumn;
import org.example.aitest.utils.DateUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 将excel文件内容按顺序set到实体类中
 * 返回的是实体类的集合，不包括id
 * */
@Component
public class ExcelUtil {


    /**
     * key:对应第几张表
     * value:key:对应表每行的数据类型结构等信息
     * */
    static Map<Integer,List<TableColumn>> staticMap = new HashMap<>();

    /**
     * key:对应第几张表
     * value:key对应选取的第几行
     *       value:key:对应列的中文首拼音
     *             value:对应列的数据
     * */
    static Map<Integer,Map<Integer,Map<String,String>>> stMap = new HashMap<>();

    //合并行多加的列数
    static Integer addCelNum = 0;

    //合并列加的列数
    static Integer addRowNum = 0;
    //合并行数
    static int hbhNum = 0;
    //表名行合并列出现次数
    static int hblNum = 0;

    @Autowired
    public DynamicTableGenerator dynamicTableGenerator;
    @Autowired
    public DynamicDataService dataService;
    
    /**
     * @description: TODO 比较上传的两个excel文件的差异
     * @author zb
     * @date 2025/8/22
     * @version: 1.0 参数1：旧文件，参数2：新文件，参数3：选择的确定数据行，参数4：选择的以哪一列为基础比较
    */ 
    public static List<EHL> oct(MultipartFile oldFile,MultipartFile newFile,Integer sl,Integer rl) throws IOException {
        String oldName = oldFile.getOriginalFilename();
        String newName = newFile.getOriginalFilename();
        String newLa = newName.substring(newName.lastIndexOf(".") + 1);
        
        if(newLa.equals("xlsx")){
            XSSFWorkbook newExcel = new XSSFWorkbook(newFile.getInputStream());
            //暂定只比较一张表
            XSSFSheet newSheet = newExcel.getSheetAt(0);
            List<EHL> list = new ArrayList<>();
            //newFile将需要比较的列设置背景色设置为标准色的浅蓝色
            //是否确定起始行
            if(sl != null && sl >= 0){
                XSSFRow row = newSheet.getRow(sl);
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i < lastCellNum; i++) {
                    XSSFCell cell = row.getCell(i);
                    if(cell == null){
                        continue;
                    }
                    //设置list中不同的行列信息（newFile中做标记）
                    if(rl == null || rl < 0) {
                        Boolean compareXlsx = isCompareXlsx(cell);
                        if(!compareXlsx){
                            continue;
                        }
                        nco(sl, i, oldFile, newFile, list);
                    }else {
                        lco(sl,rl,oldFile,newFile,list);
                        break;
                    }
                }
            }else {
                //只需要知道选择的哪一行
                List<TableMsg> aLong = getLong(newFile, -1,false);
                TableMsg tableMsg = aLong.get(0);
                Integer hNo = tableMsg.getHNo();

                XSSFRow row = newSheet.getRow(hNo);
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i < lastCellNum; i++) {
                    XSSFCell cell = row.getCell(i);
                    if(cell == null){
                        continue;
                    }
                    //设置list中不同的行列信息（newFile中做标记）
                    if(rl == null || rl < 0) {
                        Boolean compareXlsx = isCompareXlsx(cell);
                        if(!compareXlsx){
                            continue;
                        }
                        nco(hNo, i, oldFile, newFile, list);
                    }else {
                        lco(hNo,rl,oldFile,newFile,list);
                        break;
                    }
                }
            }

            return list;
        } else if (newLa.equals("xls")) {
            HSSFWorkbook newExcel = new HSSFWorkbook(newFile.getInputStream());
            //暂定只比较一张表
            HSSFSheet newSheet = newExcel.getSheetAt(0);
            List<EHL> list = new ArrayList<>();
            //newFile将需要比较的列设置背景色设置为标准色的浅蓝色
            //是否确定起始行
            if(sl != null && sl >= 0){
                HSSFRow row = newSheet.getRow(sl);
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i < lastCellNum; i++) {
                    HSSFCell cell = row.getCell(i);
                    if(cell == null){
                        continue;
                    }
                    Boolean compareXlsx = isCompareXls(cell);
                    if(!compareXlsx){
                        continue;
                    }
                    //设置list中不同的行列信息（newFile中做标记）
                    if(rl == null || rl < 0) {
                        nco(sl, i, oldFile, newFile, list);
                    }else {
                        lco(sl,rl,oldFile,newFile,list);
                        break;
                    }
                }
            }else {
                //只需要知道选择的哪一行
                List<TableMsg> aLong = getLong(newFile, -1,false);
                TableMsg tableMsg = aLong.get(0);
                Integer hNo = tableMsg.getHNo();

                HSSFRow row = newSheet.getRow(hNo);
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i < lastCellNum; i++) {
                    HSSFCell cell = row.getCell(i);
                    if(cell == null){
                        continue;
                    }
                    Boolean compareXlsx = isCompareXls(cell);
                    if(!compareXlsx){
                        continue;
                    }
                    //设置list中不同的行列信息（newFile中做标记）
                    if(rl == null || rl < 0) {
                        nco(hNo, i, oldFile, newFile, list);
                    }else {
                        lco(hNo,rl,oldFile,newFile,list);
                        break;
                    }
                }
            }

            return list;
        }
        return null;
    }

    /**
     * @description: TODO 新excel与旧excel比较辅助方法(比较一列)
     * @author zb
     * @date 2025/8/22
     * @version: 1.0 参数1：确定的起始比较行，参数2：新表哪一列数据拿来比较，参数3：旧文件，参数4：新文件
     * @return 返回数据为不相同的行列
    */
    private static List<EHL> nco(Integer startH, Integer l, MultipartFile oldFile, MultipartFile newFile, List<EHL> list) throws IOException {
        String oldName = oldFile.getOriginalFilename();
        String newName = newFile.getOriginalFilename();
        String oldLa = oldName.substring(oldName.lastIndexOf(".") + 1);
        String newLa = newName.substring(newName.lastIndexOf(".") + 1);

        if(newLa.equals("xlsx")){
            XSSFWorkbook newExcel = new XSSFWorkbook(newFile.getInputStream());
            XSSFSheet newSheet = newExcel.getSheetAt(0);
            int lastRowNum = newSheet.getLastRowNum();
            String stringCellValue;
            for (int i = startH+1; i <= lastRowNum; i++) {
                EHL ehl = new EHL();
                XSSFRow row = newSheet.getRow(i);
                XSSFCell cell = row.getCell(l);
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case FORMULA:
                            //获取公式计算后的值
                            XSSFFormulaEvaluator evaluator = newExcel.getCreationHelper().createFormulaEvaluator();
                            CellValue evaluate = evaluator.evaluate(cell);
                            switch (evaluate.getCellType()) {
                                case NUMERIC:
                                    stringCellValue = String.valueOf(evaluate.getNumberValue());
                                    break;
                                case STRING:
                                    stringCellValue = evaluate.getStringValue();
                                    break;
                                case BOOLEAN:
                                    stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                    break;
                                case ERROR:
                                    System.out.println("值: 错误");
                                    stringCellValue = String.valueOf(evaluate.getErrorValue());
                                    break;
                                default:
                                    stringCellValue = "未知类型";
                            }
                            break;
                        default:
                            // 除了公式类型，其他类型都以原始内容输出
                            DataFormatter formatter = new DataFormatter();
                            stringCellValue = formatter.formatCellValue(cell);
                            break;
                    }
                    if(oldLa.equals("xlsx")){
                        XSSFWorkbook oldExcel = new XSSFWorkbook(oldFile.getInputStream());
                        XSSFSheet oldSheet = oldExcel.getSheetAt(0);
                        //确定与旧文件的哪一列比较（因为可能删除列操作）
                        XSSFRow row2 = newSheet.getRow(startH);
                        XSSFCell cell2 = row2.getCell(l);
                        DataFormatter formatter = new DataFormatter();
                        String cellValue = formatter.formatCellValue(cell2);
                        XSSFRow row3 = oldSheet.getRow(startH);
                        int lastCellNum = row3.getLastCellNum();
                        int ol = l;
                        for (int j = 0; j < lastCellNum; j++) {
                            XSSFCell cell1 = row3.getCell(j);
                            String cellValue1 = formatter.formatCellValue(cell1);
                            if(cellValue.equals(cellValue1)){
                                ol = j;
                                break;
                            }
                        }

                        //行比较则只能一行行往下比较
                        XSSFRow row1 = oldSheet.getRow(i);
                        XSSFCell cell1 = row1.getCell(ol);
                        Boolean isXT = true;
                        if (cell1 != null) {
                            switch (cell1.getCellType()) {
                                case FORMULA:
                                    //获取公式计算后的值
                                    XSSFFormulaEvaluator evaluator = oldExcel.getCreationHelper().createFormulaEvaluator();
                                    CellValue evaluate = evaluator.evaluate(cell1);
                                    switch (evaluate.getCellType()) {
                                        case NUMERIC:
                                            if(stringCellValue.equals(String.valueOf(evaluate.getNumberValue()))){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case STRING:
                                            if(stringCellValue.equals(evaluate.getStringValue())){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case BOOLEAN:
                                            if(stringCellValue.equals(String.valueOf(evaluate.getBooleanValue()))){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case ERROR:
                                            System.out.println("值: 错误");
                                            isXT = false;
                                            break;
                                        default:
                                            isXT = false;
                                    }
                                    break;
                                default:
                                    // 除了公式类型，其他类型都以原始内容输出
                                    if(stringCellValue.equals(formatter.formatCellValue(cell1))){
                                        isXT = true;
                                    }else {
                                        isXT = false;
                                    }
                                    break;
                            }
                        }
                        if(!isXT){
                            ehl.setH(i);
                            ehl.setL(l);
                            list.add(ehl);
                        }
                    } else if (oldLa.equals("xls")) {
                        HSSFWorkbook oldExcel = new HSSFWorkbook(oldFile.getInputStream());
                        HSSFSheet oldSheet = oldExcel.getSheetAt(0);
                        //确定与旧文件的哪一列比较（因为可能删除列操作）
                        XSSFRow row2 = newSheet.getRow(startH);
                        XSSFCell cell2 = row2.getCell(l);
                        DataFormatter formatter = new DataFormatter();
                        String cellValue = formatter.formatCellValue(cell2);
                        HSSFRow row3 = oldSheet.getRow(startH);
                        int lastCellNum = row3.getLastCellNum();
                        int ol = l;
                        for (int j = 0; j < lastCellNum; j++) {
                            HSSFCell cell1 = row3.getCell(j);
                            String cellValue1 = formatter.formatCellValue(cell1);
                            if(cellValue.equals(cellValue1)){
                                ol = j;
                                break;
                            }
                        }

                        HSSFRow row1 = oldSheet.getRow(i);
                        HSSFCell cell1 = row1.getCell(ol);
                        Boolean isXT = true;
                        if (cell1 != null) {
                            switch (cell1.getCellType()) {
                                case FORMULA:
                                    //获取公式计算后的值
                                    HSSFFormulaEvaluator evaluator = oldExcel.getCreationHelper().createFormulaEvaluator();
                                    CellValue evaluate = evaluator.evaluate(cell1);
                                    switch (evaluate.getCellType()) {
                                        case NUMERIC:
                                            if(stringCellValue.equals(String.valueOf(evaluate.getNumberValue()))){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case STRING:
                                            if(stringCellValue.equals(evaluate.getStringValue())){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case BOOLEAN:
                                            if(stringCellValue.equals(String.valueOf(evaluate.getBooleanValue()))){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case ERROR:
                                            System.out.println("值: 错误");
                                            isXT = false;
                                            break;
                                        default:
                                            isXT = false;
                                    }
                                    break;
                                default:
                                    // 除了公式类型，其他类型都以原始内容输出
                                    if(stringCellValue.equals(formatter.formatCellValue(cell1))){
                                        isXT = true;
                                    }else {
                                        isXT = false;
                                    }
                                    break;
                            }
                        }
                        if(!isXT){
                            ehl.setH(i);
                            ehl.setL(l);
                            list.add(ehl);
                        }
                    }
                }
            }
            return list;
        } else if (newLa.equals("xls")) {
            HSSFWorkbook newExcel = new HSSFWorkbook(newFile.getInputStream());
            HSSFSheet newSheet = newExcel.getSheetAt(0);
            int lastRowNum = newSheet.getLastRowNum();
            String stringCellValue;
            for (int i = startH+1; i <= lastRowNum; i++) {
                EHL ehl = new EHL();
                HSSFRow row = newSheet.getRow(i);
                HSSFCell cell = row.getCell(l);
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case FORMULA:
                            //获取公式计算后的值
                            HSSFFormulaEvaluator evaluator = newExcel.getCreationHelper().createFormulaEvaluator();
                            CellValue evaluate = evaluator.evaluate(cell);
                            switch (evaluate.getCellType()) {
                                case NUMERIC:
                                    stringCellValue = String.valueOf(evaluate.getNumberValue());
                                    break;
                                case STRING:
                                    stringCellValue = evaluate.getStringValue();
                                    break;
                                case BOOLEAN:
                                    stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                    break;
                                case ERROR:
                                    System.out.println("值: 错误");
                                    stringCellValue = String.valueOf(evaluate.getErrorValue());
                                    break;
                                default:
                                    stringCellValue = "未知类型";
                            }
                            break;
                        default:
                            // 除了公式类型，其他类型都以原始内容输出
                            DataFormatter formatter = new DataFormatter();
                            stringCellValue = formatter.formatCellValue(cell);
                            break;
                    }
                    if(oldLa.equals("xlsx")){
                        XSSFWorkbook oldExcel = new XSSFWorkbook(oldFile.getInputStream());
                        XSSFSheet oldSheet = oldExcel.getSheetAt(0);
                        //确定与旧文件的哪一列比较（因为可能删除列操作）
                        HSSFRow row2 = newSheet.getRow(startH);
                        HSSFCell cell2 = row2.getCell(l);
                        DataFormatter formatter = new DataFormatter();
                        String cellValue = formatter.formatCellValue(cell2);
                        XSSFRow row3 = oldSheet.getRow(startH);
                        int lastCellNum = row3.getLastCellNum();
                        int ol = l;
                        for (int j = 0; j < lastCellNum; j++) {
                            XSSFCell cell1 = row3.getCell(j);
                            String cellValue1 = formatter.formatCellValue(cell1);
                            if(cellValue.equals(cellValue1)){
                                ol = j;
                                break;
                            }
                        }

                        XSSFRow row1 = oldSheet.getRow(i);
                        XSSFCell cell1 = row1.getCell(ol);
                        Boolean isXT = true;
                        if (cell1 != null) {
                            switch (cell1.getCellType()) {
                                case FORMULA:
                                    //获取公式计算后的值
                                    XSSFFormulaEvaluator evaluator = oldExcel.getCreationHelper().createFormulaEvaluator();
                                    CellValue evaluate = evaluator.evaluate(cell1);
                                    switch (evaluate.getCellType()) {
                                        case NUMERIC:
                                            if(stringCellValue.equals(String.valueOf(evaluate.getNumberValue()))){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case STRING:
                                            if(stringCellValue.equals(evaluate.getStringValue())){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case BOOLEAN:
                                            if(stringCellValue.equals(String.valueOf(evaluate.getBooleanValue()))){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case ERROR:
                                            System.out.println("值: 错误");
                                            isXT = false;
                                            break;
                                        default:
                                            isXT = false;
                                    }
                                    break;
                                default:
                                    // 除了公式类型，其他类型都以原始内容输出
                                    if(stringCellValue.equals(formatter.formatCellValue(cell1))){
                                        isXT = true;
                                    }else {
                                        isXT = false;
                                    }
                                    break;
                            }
                        }
                        if(!isXT){
                            ehl.setH(i);
                            ehl.setL(l);
                            list.add(ehl);
                        }
                    } else if (oldLa.equals("xls")) {
                        HSSFWorkbook oldExcel = new HSSFWorkbook(oldFile.getInputStream());
                        HSSFSheet oldSheet = oldExcel.getSheetAt(0);
                        //确定与旧文件的哪一列比较（因为可能删除列操作）
                        HSSFRow row2 = newSheet.getRow(startH);
                        HSSFCell cell2 = row2.getCell(l);
                        DataFormatter formatter = new DataFormatter();
                        String cellValue = formatter.formatCellValue(cell2);
                        HSSFRow row3 = oldSheet.getRow(startH);
                        int lastCellNum = row3.getLastCellNum();
                        int ol = l;
                        for (int j = 0; j < lastCellNum; j++) {
                            HSSFCell cell1 = row3.getCell(j);
                            String cellValue1 = formatter.formatCellValue(cell1);
                            if(cellValue.equals(cellValue1)){
                                ol = j;
                                break;
                            }
                        }
                        HSSFRow row1 = oldSheet.getRow(i);
                        HSSFCell cell1 = row1.getCell(ol);
                        Boolean isXT = true;
                        if (cell1 != null) {
                            switch (cell1.getCellType()) {
                                case FORMULA:
                                    //获取公式计算后的值
                                    HSSFFormulaEvaluator evaluator = oldExcel.getCreationHelper().createFormulaEvaluator();
                                    CellValue evaluate = evaluator.evaluate(cell1);
                                    switch (evaluate.getCellType()) {
                                        case NUMERIC:
                                            if(stringCellValue.equals(String.valueOf(evaluate.getNumberValue()))){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case STRING:
                                            if(stringCellValue.equals(evaluate.getStringValue())){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case BOOLEAN:
                                            if(stringCellValue.equals(String.valueOf(evaluate.getBooleanValue()))){
                                                isXT = true;
                                            }else {
                                                isXT = false;
                                            }
                                            break;
                                        case ERROR:
                                            System.out.println("值: 错误");
                                            isXT = false;
                                            break;
                                        default:
                                            isXT = false;
                                    }
                                    break;
                                default:
                                    // 除了公式类型，其他类型都以原始内容输出
                                    if(stringCellValue.equals(formatter.formatCellValue(cell1))){
                                        isXT = true;
                                    }else {
                                        isXT = false;
                                    }
                                    break;
                            }
                        }
                        if(!isXT){
                            ehl.setH(i);
                            ehl.setL(l);
                            list.add(ehl);
                        }
                    }
                }
            }
            return list;
        }
        return list;
    }


    /**
     * @description: TODO 新excel与旧excel比较辅助方法(比较所有列-设置需要比较的所有列)
     * @author zb
     * @date 2025/8/22
     * @version: 1.0 参数1：确定的起始比较行，参数2：新表哪一列数据确定唯一匹配信息比较，参数3：旧文件，参数4：新文件
     * @return 返回数据为不相同的行列
     */
    private static List<EHL> lco(Integer startH,Integer l,MultipartFile oldFile, MultipartFile newFile, List<EHL> list) throws IOException {
        String oldName = oldFile.getOriginalFilename();
        String newName = newFile.getOriginalFilename();
        String oldLa = oldName.substring(oldName.lastIndexOf(".") + 1);
        String newLa = newName.substring(newName.lastIndexOf(".") + 1);
        if(newLa.equals("xlsx")){
            XSSFWorkbook newExcel = new XSSFWorkbook(newFile.getInputStream());
            XSSFSheet newSheet = newExcel.getSheetAt(0);
            XSSFRow row = newSheet.getRow(startH);
            XSSFCell cell = row.getCell(l);
            DataFormatter formatter = new DataFormatter();
            String s = formatter.formatCellValue(cell);
            int lastRowNum = newSheet.getLastRowNum();
            for (int i = startH+1; i <= lastRowNum; i++) {
                XSSFRow row1 = newSheet.getRow(i);
                XSSFCell cell1 = row1.getCell(l);
                String newCellValue = getCellValueXlsx(newExcel, formatter, cell1);

                if(oldLa.equals("xlsx")){
                    XSSFWorkbook oldExcel = new XSSFWorkbook(oldFile.getInputStream());
                    XSSFSheet sheet = oldExcel.getSheetAt(0);
                    XSSFRow row2 = sheet.getRow(startH);
                    int lastCellNum = row2.getLastCellNum();
                    //确定是与旧表的哪一列对应
                    Integer ol = 0;
                    for (int j = 0; j < lastCellNum; j++) {
                        XSSFCell cell2 = row2.getCell(j);
                        String oldValue = getCellValueXlsx(oldExcel, formatter, cell2);
                        if(oldValue.equals(s)){
                            ol = j;
                            break;
                        }
                    }

                    //确定比较旧表哪一行数据
                    Integer oldH = 0;
                    int lastRowNum1 = sheet.getLastRowNum();
                    for (int j = startH+1; j < lastRowNum1; j++) {
                        XSSFRow row3 = sheet.getRow(j);
                        XSSFCell cell2 = row3.getCell(ol);
                        String oldCellValue = getCellValueXlsx(oldExcel, formatter, cell2);
                        if(newCellValue.equals(oldCellValue)){
                            oldH = j;
                            break;
                        }
                    }

                    int lastCellNum1 = row.getLastCellNum();
                    for (int j = 0; j < lastCellNum1; j++) {
                        if(j == l){
                            //跳过确定匹配比较列
                            continue;
                        }
                        XSSFCell cell5 = row1.getCell(j);
                        String newV = getCellValueXlsx(newExcel, formatter, cell5);
                        XSSFCell cell2 = row.getCell(j);
                        String newT = getCellValueXlsx(newExcel, formatter, cell2);
                        for (int k = 0; k < lastCellNum; k++) {
                            XSSFCell cell3 = row2.getCell(k);
                            String oldT = getCellValueXlsx(oldExcel, formatter, cell3);
                            if(newT.equals(oldT)){
                                //头相同
                                XSSFRow row3 = sheet.getRow(oldH);
                                XSSFCell cell4 = row3.getCell(k);
                                String oldV = getCellValueXlsx(oldExcel, formatter, cell4);
                                if(!newV.equals(oldV)){
                                    EHL ehl = new EHL();
                                    ehl.setH(i);
                                    ehl.setL(j);
                                    list.add(ehl);
                                }
                                break;
                            }
                        }
                    }
                } else if (oldLa.equals("xls")) {
                    HSSFWorkbook oldExcel = new HSSFWorkbook(oldFile.getInputStream());
                    HSSFSheet sheet = oldExcel.getSheetAt(0);
                    HSSFRow row2 = sheet.getRow(startH);
                    int lastCellNum = row2.getLastCellNum();
                    //确定是与旧表的哪一列对应
                    Integer ol = 0;
                    for (int j = 0; j < lastCellNum; j++) {
                        HSSFCell cell2 = row2.getCell(j);
                        String oldValue = getCellValueXls(oldExcel, formatter, cell2);
                        if(oldValue.equals(s)){
                            ol = j;
                            break;
                        }
                    }

                    //确定比较旧表哪一行数据
                    Integer oldH = 0;
                    int lastRowNum1 = sheet.getLastRowNum();
                    for (int j = startH+1; j < lastRowNum1; j++) {
                        HSSFRow row3 = sheet.getRow(j);
                        HSSFCell cell2 = row3.getCell(ol);
                        String oldCellValue = getCellValueXls(oldExcel, formatter, cell2);
                        if(newCellValue.equals(oldCellValue)){
                            oldH = j;
                            break;
                        }
                    }

                    int lastCellNum1 = row.getLastCellNum();
                    for (int j = 0; j < lastCellNum1; j++) {
                        if(j == l){
                            //跳过确定匹配比较列
                            continue;
                        }
                        XSSFCell cell5 = row1.getCell(j);
                        String newV = getCellValueXlsx(newExcel, formatter, cell5);
                        XSSFCell cell2 = row.getCell(j);
                        String newT = getCellValueXlsx(newExcel, formatter, cell2);
                        for (int k = 0; k < lastCellNum; k++) {
                            HSSFCell cell3 = row2.getCell(k);
                            String oldT = getCellValueXls(oldExcel, formatter, cell3);
                            if(newT.equals(oldT)){
                                //头相同
                                HSSFRow row3 = sheet.getRow(oldH);
                                HSSFCell cell4 = row3.getCell(k);
                                String oldV = getCellValueXls(oldExcel, formatter, cell4);
                                if(!newV.equals(oldV)){
                                    EHL ehl = new EHL();
                                    ehl.setH(i);
                                    ehl.setL(j);
                                    list.add(ehl);
                                }
                                break;
                            }
                        }
                    }
                }
            }
            return list;
        } else if (newLa.equals("xls")) {
            HSSFWorkbook newExcel = new HSSFWorkbook(newFile.getInputStream());
            HSSFSheet newSheet = newExcel.getSheetAt(0);
            HSSFRow row = newSheet.getRow(startH);
            HSSFCell cell = row.getCell(l);
            DataFormatter formatter = new DataFormatter();
            String s = formatter.formatCellValue(cell);
            int lastRowNum = newSheet.getLastRowNum();
            for (int i = startH+1; i <= lastRowNum; i++) {
                HSSFRow row1 = newSheet.getRow(i);
                HSSFCell cell1 = row1.getCell(l);
                String newCellValue = getCellValueXls(newExcel, formatter, cell1);

                if(oldLa.equals("xlsx")){
                    XSSFWorkbook oldExcel = new XSSFWorkbook(oldFile.getInputStream());
                    XSSFSheet sheet = oldExcel.getSheetAt(0);
                    XSSFRow row2 = sheet.getRow(startH);
                    int lastCellNum = row2.getLastCellNum();
                    //确定是与旧表的哪一列对应
                    Integer ol = 0;
                    for (int j = 0; j < lastCellNum; j++) {
                        XSSFCell cell2 = row2.getCell(j);
                        String oldValue = getCellValueXlsx(oldExcel, formatter, cell2);
                        if(oldValue.equals(s)){
                            ol = j;
                            break;
                        }
                    }

                    //确定比较旧表哪一行数据
                    Integer oldH = 0;
                    int lastRowNum1 = sheet.getLastRowNum();
                    for (int j = startH+1; j < lastRowNum1; j++) {
                        XSSFRow row3 = sheet.getRow(j);
                        XSSFCell cell2 = row3.getCell(ol);
                        String oldCellValue = getCellValueXlsx(oldExcel, formatter, cell2);
                        if(newCellValue.equals(oldCellValue)){
                            oldH = j;
                            break;
                        }
                    }

                    int lastCellNum1 = row.getLastCellNum();
                    for (int j = 0; j < lastCellNum1; j++) {
                        if(j == l){
                            //跳过确定匹配比较列
                            continue;
                        }
                        HSSFCell cell5 = row1.getCell(j);
                        String newV = getCellValueXls(newExcel, formatter, cell5);
                        HSSFCell cell2 = row.getCell(j);
                        String newT = getCellValueXls(newExcel, formatter, cell2);
                        for (int k = 0; k < lastCellNum; k++) {
                            XSSFCell cell3 = row2.getCell(k);
                            String oldT = getCellValueXlsx(oldExcel, formatter, cell3);
                            if(newT.equals(oldT)){
                                //头相同
                                XSSFRow row3 = sheet.getRow(oldH);
                                XSSFCell cell4 = row3.getCell(k);
                                String oldV = getCellValueXlsx(oldExcel, formatter, cell4);
                                if(!newV.equals(oldV)){
                                    EHL ehl = new EHL();
                                    ehl.setH(i);
                                    ehl.setL(j);
                                    list.add(ehl);
                                }
                                break;
                            }
                        }
                    }
                } else if (oldLa.equals("xls")) {
                    HSSFWorkbook oldExcel = new HSSFWorkbook(oldFile.getInputStream());
                    HSSFSheet sheet = oldExcel.getSheetAt(0);
                    HSSFRow row2 = sheet.getRow(startH);
                    int lastCellNum = row2.getLastCellNum();
                    //确定是与旧表的哪一列对应
                    Integer ol = 0;
                    for (int j = 0; j < lastCellNum; j++) {
                        HSSFCell cell2 = row2.getCell(j);
                        String oldValue = getCellValueXls(oldExcel, formatter, cell2);
                        if(oldValue.equals(s)){
                            ol = j;
                            break;
                        }
                    }

                    //确定比较旧表哪一行数据
                    Integer oldH = 0;
                    int lastRowNum1 = sheet.getLastRowNum();
                    for (int j = startH+1; j < lastRowNum1; j++) {
                        HSSFRow row3 = sheet.getRow(j);
                        HSSFCell cell2 = row3.getCell(ol);
                        String oldCellValue = getCellValueXls(oldExcel, formatter, cell2);
                        if(newCellValue.equals(oldCellValue)){
                            oldH = j;
                            break;
                        }
                    }

                    int lastCellNum1 = row.getLastCellNum();
                    for (int j = 0; j < lastCellNum1; j++) {
                        if(j == l){
                            //跳过确定匹配比较列
                            continue;
                        }
                        HSSFCell cell5 = row1.getCell(j);
                        String newV = getCellValueXls(newExcel, formatter, cell5);
                        HSSFCell cell2 = row.getCell(j);
                        String newT = getCellValueXls(newExcel, formatter, cell2);
                        for (int k = 0; k < lastCellNum; k++) {
                            HSSFCell cell3 = row2.getCell(k);
                            String oldT = getCellValueXls(oldExcel, formatter, cell3);
                            if(newT.equals(oldT)){
                                //头相同
                                HSSFRow row3 = sheet.getRow(oldH);
                                HSSFCell cell4 = row3.getCell(k);
                                String oldV = getCellValueXls(oldExcel, formatter, cell4);
                                if(!newV.equals(oldV)){
                                    EHL ehl = new EHL();
                                    ehl.setH(i);
                                    ehl.setL(j);
                                    list.add(ehl);
                                }
                                break;
                            }
                        }
                    }
                }
            }
            return list;
        }
        return null;
    }

    private static String getCellValueXlsx(XSSFWorkbook newExcel, DataFormatter formatter, XSSFCell cell1) {
        String stringCellValue = null;
        if (cell1 != null) {
            switch (cell1.getCellType()) {
                case FORMULA:
                    //获取公式计算后的值
                    XSSFFormulaEvaluator evaluator = newExcel.getCreationHelper().createFormulaEvaluator();
                    CellValue evaluate = evaluator.evaluate(cell1);
                    switch (evaluate.getCellType()) {
                        case NUMERIC:
                            stringCellValue = String.valueOf(evaluate.getNumberValue());
                            break;
                        case STRING:
                            stringCellValue = evaluate.getStringValue();
                            break;
                        case BOOLEAN:
                            stringCellValue = String.valueOf(evaluate.getBooleanValue());
                            break;
                        case ERROR:
                            System.out.println("值: 错误");
                            stringCellValue = String.valueOf(evaluate.getErrorValue());
                            break;
                        default:
                            stringCellValue = "未知类型";
                    }
                    break;
                default:
                    // 除了公式类型，其他类型都以原始内容输出
                    stringCellValue = formatter.formatCellValue(cell1);
                    break;
            }
        }
        return stringCellValue;
    }

    private static String getCellValueXls(HSSFWorkbook newExcel, DataFormatter formatter, HSSFCell cell1) {
        String stringCellValue = null;
        if (cell1 != null) {
            switch (cell1.getCellType()) {
                case FORMULA:
                    //获取公式计算后的值
                    HSSFFormulaEvaluator evaluator = newExcel.getCreationHelper().createFormulaEvaluator();
                    CellValue evaluate = evaluator.evaluate(cell1);
                    switch (evaluate.getCellType()) {
                        case NUMERIC:
                            stringCellValue = String.valueOf(evaluate.getNumberValue());
                            break;
                        case STRING:
                            stringCellValue = evaluate.getStringValue();
                            break;
                        case BOOLEAN:
                            stringCellValue = String.valueOf(evaluate.getBooleanValue());
                            break;
                        case ERROR:
                            System.out.println("值: 错误");
                            stringCellValue = String.valueOf(evaluate.getErrorValue());
                            break;
                        default:
                            stringCellValue = "未知类型";
                    }
                    break;
                default:
                    // 除了公式类型，其他类型都以原始内容输出
                    stringCellValue = formatter.formatCellValue(cell1);
                    break;
            }
        }
        return stringCellValue;
    }

    /**
     * 用传入的文件某一列来比较之前用excel自动建表的内容，若匹配上则将数据库对应内容填入该表对应下面，如果匹配上，但是后面内容没有对应填入位置则依次往后填入
     * 返回结果为，map,key为确定的第几张表
     * value为map结构，valueKey为对应excel表的第几行数据，value的value为数据集
     * */
    public static List<TableMsg> compare(MultipartFile file,Integer sl,Integer ty) throws Exception {
        String name = file.getOriginalFilename();

        // 文件后缀
        String la = name.substring(name.lastIndexOf(".")+1);
        // 处理文件
        if(la.equals("xlsx")){
            XSSFWorkbook excel = new XSSFWorkbook(file.getInputStream());

            List<TableMsg> tableMsgs = new ArrayList<>();
            if(sl == null || sl < 0) {
                // 实际上不需要其返回数据，只需要其设置的全局map对象
                setValuesXLSX(excel,sl,ty);
            }
            if(sl == null || sl < 0) {
                for (Integer i : stMap.keySet()) {
                    Map<Integer, Map<String, String>> integerListMap = stMap.get(i);
                    XSSFSheet sheet = excel.getSheetAt(i);

                    for (Integer integer : integerListMap.keySet()) {
                        // 以integer的下一行开始找数据，行为基础扫描
                        //总行数
                        int lastRowNum = sheet.getLastRowNum();
                        // 表名行
                        XSSFRow row1 = sheet.getRow(integer);

                        int lastCellNum1 = row1.getLastCellNum();
                        // 以列为基础，一列一列往后找
                        for (int j = 0; j < lastCellNum1; j++) {
                            TableMsg tableMsg = null;

                            // 只有在有数据的列才需要此数据
                            String hName = null;
                            List<Object> list = new ArrayList<>();

                            for (int k = integer + 1; k <= lastRowNum; k++) {
                                XSSFRow row = sheet.getRow(k);
                                XSSFCell cell = row.getCell(j);
                                DataFormatter formatter = new DataFormatter();
                                if (cell != null) {
                                    // 取出该列原始内容
                                    String stringCellValue = formatter.formatCellValue(cell);
                                    if (StringUtils.isNotEmpty(stringCellValue)) {
                                        list.add(stringCellValue);
                                    }
                                }
                                // 只有第一次进入才需要初始化下面数据，可加if条件为k == integer+1
                                if (k == integer + 1) {
                                    /**
                                     * 确定的表行数据，用来封装返回
                                     * 例如：以excel表第三行为基础往下做数据匹配
                                     * 第一列为订单编号，往下的行为对应的订单编号，则取出：订单编号这个数据，后面加上该行下所有内容
                                     * */
                                    XSSFCell cell1 = row1.getCell(j);
                                    String stringCellValue1 = formatter.formatCellValue(cell1);
                                    if (StringUtils.isEmpty(stringCellValue1)) {
                                        continue;
                                    }
                                    String firstLetters = PinyinUtils.getFirstLetters(stringCellValue1);
                                    hName = PinyinUtils.sanitizeTableName(firstLetters);

                                    tableMsg = new TableMsg();
                                    tableMsg.setCellYName(stringCellValue1);
                                    tableMsg.setBNo(i);
                                    tableMsg.setHNo(integer);
                                    tableMsg.setLNo(j);
                                    tableMsg.setCellName(hName);
                                }
                            }
                            if (tableMsg != null) {
                                tableMsg.setList(list);
                                tableMsgs.add(tableMsg);
                            }
                        }
                    }
                }
            }else {
                XSSFSheet sheet = excel.getSheetAt(0);
                XSSFRow row = sheet.getRow(sl);
                int lastRowNum = sheet.getLastRowNum();
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i < lastCellNum; i++) {
                    TableMsg tableMsg = null;
                    List<Object> list = new ArrayList<>();
                    for (int j = sl+1; j <= lastRowNum; j++) {
                        XSSFCell cell = row.getCell(i);
                        if(cell == null){
                            //结束本列
                            break;
                        }
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);
                        if(StringUtils.isEmpty(stringCellValue)){
                            //结束本列
                            break;
                        }
                        tableMsg = new TableMsg();
                        tableMsg.setBNo(0);
                        tableMsg.setLNo(i);
                        XSSFRow row1 = sheet.getRow(j);
                        XSSFCell cell1 = row1.getCell(i);
                        String stringCellValue1 = formatter.formatCellValue(cell1);
                        tableMsg.setHNo(sl);
                        tableMsg.setCellName(PinyinUtils.sanitizeTableName(PinyinUtils.getFirstLetters(stringCellValue)));
                        tableMsg.setCellYName(stringCellValue);
                        list.add(stringCellValue1);
                    }
                    if(tableMsg != null) {
                        tableMsg.setList(list);
                        tableMsgs.add(tableMsg);
                    }
                }

            }

            stMap.clear();
            staticMap.clear();

            return tableMsgs;
        }else if (la.equals("xls")) {
            HSSFWorkbook excel = new HSSFWorkbook(file.getInputStream());

            // 实际上不需要其返回数据，只需要其设置的全局map对象
            setValuesXLS(excel);

            List<TableMsg> tableMsgs = new ArrayList<>();
            for (Integer i : stMap.keySet()) {
                Map<Integer, Map<String, String>> integerListMap = stMap.get(i);
                HSSFSheet sheet = excel.getSheetAt(i);

                for (Integer integer : integerListMap.keySet()) {
                    // 以integer的下一行开始找数据，行为基础扫描
                    //总行数
                    int lastRowNum = sheet.getLastRowNum();
                    // 表名行
                    HSSFRow row1 = sheet.getRow(integer);

                    int lastCellNum1 = row1.getLastCellNum();
                    // 以列为基础，一列一列往后找
                    for (int j = 0; j < lastCellNum1; j++) {
                        TableMsg tableMsg = null;

                        // 只有在有数据的列才需要此数据
                        String hName = null;
                        List<Object> list = new ArrayList<>();

                        for (int k = integer+1; k <= lastRowNum; k++) {
                            HSSFRow row = sheet.getRow(k);
                            HSSFCell cell = row.getCell(j);
                            DataFormatter formatter = new DataFormatter();
                            if(cell != null){
                                // 取出该列原始内容
                                String stringCellValue = formatter.formatCellValue(cell);
                                if(StringUtils.isNotEmpty(stringCellValue)){
                                    list.add(stringCellValue);
                                }
                            }
                            // 只有第一次进入才需要初始化下面数据，可加if条件为k == integer+1
                            if(k == integer+1){
                                /**
                                 * 确定的表行数据，用来封装返回
                                 * 例如：以excel表第三行为基础往下做数据匹配
                                 * 第一列为订单编号，往下的行为对应的订单编号，则取出：订单编号这个数据，后面加上该行下所有内容
                                 * */
                                HSSFCell cell1 = row1.getCell(j);
                                String stringCellValue1 = formatter.formatCellValue(cell1);
                                if(StringUtils.isEmpty(stringCellValue1)){
                                    continue;
                                }
                                String firstLetters = PinyinUtils.getFirstLetters(stringCellValue1);
                                hName = PinyinUtils.sanitizeTableName(firstLetters);

                                tableMsg = new TableMsg();
                                tableMsg.setCellYName(stringCellValue1);
                                tableMsg.setBNo(i);
                                tableMsg.setHNo(integer);
                                tableMsg.setLNo(j);
                                tableMsg.setCellName(hName);
                            }
                        }
                        if(tableMsg != null){
                            tableMsg.setList(list);
                            tableMsgs.add(tableMsg);
                        }
                    }
                }
            }

            stMap.clear();
            staticMap.clear();

            return tableMsgs;
        }
        return null;
    }

    /**
     * 判断指定单元格是否属于合并区域
     */
    public static boolean isMergedCell(Sheet sheet, int row, int col) {
        for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
            if (mergedRegion.isInRange(row, col)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 获取单元格所属的合并区域（如果存在）
     */
    public static CellRangeAddress getMergedRegion(Sheet sheet, int row, int col) {
        for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
            if (mergedRegion.isInRange(row, col)) {
                return mergedRegion;
            }
        }
        return null; // 未合并
    }

    /**
     * 通过excel文件在数据库中添加excel对应表，并将excel文件内容对应填入，以第一次出现的最长一行为基础建表，以该行往下内容为数据填入
     * */
    public void createTableToExcel(MultipartFile file){
        String name = file.getOriginalFilename();

        // 文件后缀
        String la = name.substring(name.lastIndexOf(".")+1);
        try {
            if(la.equals("xlsx")){

                XSSFWorkbook excel = new XSSFWorkbook(file.getInputStream());

                Map<String, Object> tableColumnsXLSX = getTableColumnsXLSX(excel);
                // 开始设置生成数据库表
                if (tableColumnsXLSX.containsKey("table")) { //先创建数据库
                    Map<Integer, List<TableColumn>> tableColumns = (Map<Integer, List<TableColumn>>) tableColumnsXLSX.get("table");
                    for (Integer integer : tableColumns.keySet()) {
                        // 表名
                        String tableName = PinyinUtils.getFirstLetters(name + "_" + integer);
                        //对表名处理
                        String res_name = PinyinUtils.sanitizeTableName(tableName);
                        //表格属性集合
                        List<TableColumn> tableColumns1 = tableColumns.get(integer);
                        //一个excel文件里面可能有多个excel表，一个表就生成一个数据库
                        dynamicTableGenerator.createTable(res_name, tableColumns1,name,1);
                    }
                }

                if (tableColumnsXLSX.containsKey("tableIn")) {//生成数据库后动态添加数据
                    Map<Integer, List<List<Map<String, Object>>>> tab = (Map<Integer, List<List<Map<String, Object>>>>) tableColumnsXLSX.get("tableIn");
                    for (Integer integer : tab.keySet()) {
                        // 表名
                        String tableName = PinyinUtils.getFirstLetters(name + "_" + integer);
                        //对表名处理
                        String res_name = PinyinUtils.sanitizeTableName(tableName);
                        // 一张表所有行的数据
                        List<List<Map<String, Object>>> lists = tab.get(integer);
                        for (List<Map<String, Object>> list : lists) { //只要是一行就行
                            //一行的数据
                            for (int i = 0; i < list.size(); i++) {
                                //按表列名动态添加数据
                                Map<String, Object> objectMap = list.get(i);
                                dataService.insertData(res_name, objectMap);
                            }
                        }
                    }
                }
            }else if(la.equals("xls")){
                HSSFWorkbook excel = new HSSFWorkbook(file.getInputStream());
                Map<String, Object> tableColumnsXLSX = ExcelUtil.getTableColumnsXLS(excel);
                // 开始设置生成数据库表
                if (tableColumnsXLSX.containsKey("table")) { //先创建数据库
                    Map<Integer, List<TableColumn>> tableColumns = (Map<Integer, List<TableColumn>>) tableColumnsXLSX.get("table");
                    for (Integer integer : tableColumns.keySet()) {
                        // 表名
                        String tableName = PinyinUtils.getFirstLetters(name + "_" + integer);
                        //对表名处理
                        String res_name = PinyinUtils.sanitizeTableName(tableName);
                        //表格属性集合
                        List<TableColumn> tableColumns1 = tableColumns.get(integer);
                        //一个excel文件里面可能有多个excel表，一个表就生成一个数据库
                        dynamicTableGenerator.createTable(res_name, tableColumns1,name,1);
                    }
                }

                if (tableColumnsXLSX.containsKey("tableIn")) {//生成数据库后动态添加数据
                    Map<Integer, List<List<Map<String, Object>>>> tab = (Map<Integer, List<List<Map<String, Object>>>>) tableColumnsXLSX.get("tableIn");
                    for (Integer integer : tab.keySet()) {
                        // 表名
                        String tableName = PinyinUtils.getFirstLetters(name + "_" + integer);
                        //对表名处理
                        String res_name = PinyinUtils.sanitizeTableName(tableName);
                        // 一张表所有行的数据
                        List<List<Map<String, Object>>> lists = tab.get(integer);
                        for (List<Map<String, Object>> list : lists) { //只要是一行就行
                            //一行的数据
                            for (int i = 0; i < list.size(); i++) {
                                //按表列名动态添加数据
                                Map<String, Object> objectMap = list.get(i);
                                dataService.insertData(res_name, objectMap);
                            }
                        }
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**  TODO 此方法调用要求数据库先建好表后，会按顺序依次添加
     * excel:后缀为.xls的excel表
     * clazz:要set的对象实体类
     * bb:读取第几张表
     * dh:读取第几行来确定循环次数，即确定set次数
     * bh:从第几行开始遍历读取数据
     * bl:从第几列开始便利读取数据
     * 返回类型即为传入类型
     * */
    public static <T> List<T> xls(HSSFWorkbook excel, Class<T> clazz, Integer bb, Integer dh, Integer bh, Integer bl) throws Exception {
        HSSFSheet sheet = excel.getSheetAt(bb-1);
        HSSFRow row = sheet.getRow(dh);
        // 列数
        int lastCellNum = row.getLastCellNum();
        //行数
        int lastRowNum = sheet.getLastRowNum();

        //所有行的数据集合对象
        List<T> objects = new ArrayList<>();
        for (int i = bh-1; i <= lastRowNum; i++) { //从第四行开始遍历
            //一行的数据集合
            List<String> list = new ArrayList<>();
            T object = clazz.newInstance();

            HSSFRow row1 = sheet.getRow(i);
            for (int j = bl-1; j < lastCellNum; j++) {
                HSSFCell cell = row1.getCell(j);
                String stringCellValue = "空";
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case STRING:
                            stringCellValue = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                stringCellValue = DateUtils.parseDate2String(cell.getDateCellValue());
                            } else {
                                // 数字以原格式输出，避免添加0
                                DataFormatter formatter = new DataFormatter();
                                stringCellValue = formatter.formatCellValue(cell);
                            }
                            break;
                        case BOOLEAN:
                            stringCellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case FORMULA:
                            stringCellValue = cell.getCellFormula();
                            break;
                        default:
                            stringCellValue = "空";
                    }
                }
                list.add(stringCellValue);
            }
            invokeAllSetters(object,list);
            if(isObjNull(object)){
                objects.add(object);
            }
        }
        return objects;
    }

    /**
     * obj 目标对象
     * values 要设置的值
     * */
    public static void invokeAllSetters(Object obj, List<String> values){
        Class<?> clazz = obj.getClass();
        //获取所有字段（按申明顺序）
        List<Field> fields = Arrays.asList(clazz.getDeclaredFields());
        // 获取所有set方法并按方法名排序（保持申明顺序）
        Map<String, Method> setterMap = Arrays.stream(clazz.getMethods()).filter(method -> method.getName().startsWith("set") &&
                        method.getParameterCount() == 1 && method.getReturnType() == void.class)
                .collect(Collectors.toMap(
                        Method::getName,
                        m -> m,
                        (m1,m2) -> m1)); //如果有重名方法，取第一个

        // 准备要调用的set方法列表(按字段顺序)
        List<Method> orderedSetters = new ArrayList<>();

        for (Field field : fields) {
            String setterName = "set" + capitalize(field.getName());
            Method setter = setterMap.get(setterName);
            if(setter != null){
                // 跳过第一个setId
                if("setId".equals(setterName)){
                    continue;
                }
                orderedSetters.add(setter);
            }

        }
        //依次调用set方法
        for (int i = 0; i < orderedSetters.size(); i++) {

            String stringValue = values.get(i);
            if("空".equals(stringValue)){
                continue;
            }
            Method setter = orderedSetters.get(i);

            try {
                Class<?> parameterType = setter.getParameterTypes()[0];
                Object converted = convertStringToType(stringValue, parameterType);
                setter.invoke(obj,converted);
            }catch (Exception e){
                throw new RuntimeException("设置属性[" + setter.getName() +
                        "]时出错: " + e.getMessage(), e);
            }
        }
    }

    private static String capitalize(String str) {
        if (str == null || str.isEmpty()) {
            return str;
        }
        return str.substring(0, 1).toUpperCase() + str.substring(1);
    }

    // 将字符串转化为目标类型
    private static Object convertStringToType(String value, Class<?> targetType){
        if(StringUtils.isEmpty(value)){
            return null;
        }

        try {
            if (targetType == String.class) {
                return value;
            } else if (targetType == Integer.class || targetType == int.class) {
                return Integer.parseInt(value);
            } else if (targetType == Long.class || targetType == long.class) {
                return Long.parseLong(value);
            } else if (targetType == Double.class || targetType == double.class) {
                return Double.parseDouble(value);
            } else if (targetType == Float.class || targetType == float.class) {
                return Float.parseFloat(value);
            } else if (targetType == Boolean.class || targetType == boolean.class) {
                return Boolean.parseBoolean(value);
            } else if (targetType == BigDecimal.class) {
                // 移除所有非数字字符（保留小数点、负号和数字）
                String cleanedValue = value.replaceAll("[^\\d.-]", "");
                return new BigDecimal(cleanedValue);
            } else if (targetType == Date.class) {
                try {
                    return com.alibaba.fastjson2.util.DateUtils.parseDate(value);
                } catch (Exception e) {
                    // 尝试下一种格式
                }
                throw new IllegalArgumentException("无法解析日期: " + value);
            } else {
                throw new IllegalArgumentException("不支持的类型转换: String -> " + targetType.getName());
            }
        }catch (Exception e){
            throw new IllegalArgumentException("无法将值[" + value +
                    "]转换为类型[" + targetType.getName() + "]", e);
        }
    }

    public static <T> List<T> xlsx(XSSFWorkbook excel, Class<T> clazz, Integer bb, Integer dh, Integer bh, Integer bl) throws Exception {
        XSSFSheet sheet = excel.getSheetAt(bb - 1);
        XSSFRow row = sheet.getRow(dh);
        // 列数
        int lastCellNum = row.getLastCellNum();
        //行数
        int lastRowNum = sheet.getLastRowNum();

        //所有行的数据集合对象
        List<T> objects = new ArrayList<>();
        for (int i = bh-1; i <= lastRowNum; i++) {
            //一行的数据集合
            List<String> list = new ArrayList<>();
            T object = clazz.newInstance();

            XSSFRow row1 = sheet.getRow(i);
            for (int j = bl-1; j < lastCellNum; j++) {
                XSSFCell cell = row1.getCell(j);
                String stringCellValue = "空";
                if (cell != null) {
                    if(cell.getCellType() == CellType.STRING){
                        stringCellValue = cell.getStringCellValue();
                    } else if (cell.getCellType() == CellType.NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            stringCellValue = DateUtils.parseDate2String(cell.getDateCellValue());
                        } else {
                            // 数字以原格式输出，避免添加0
                            DataFormatter formatter = new DataFormatter();
                            stringCellValue = formatter.formatCellValue(cell);
                        }
                    } else if (cell.getCellType() == CellType.BOOLEAN) {
                        stringCellValue = String.valueOf(cell.getBooleanCellValue());
                    } else if (cell.getCellType() == CellType.FORMULA) {
                        stringCellValue = cell.getCellFormula();
                    }else {
                        stringCellValue = "空";
                    }
                }
                list.add(stringCellValue);
            }
            invokeAllSetters(object,list);
            if(isObjNull(object)){
                objects.add(object);
            }
        }
        return objects;
    }

    // 判断对象是否全为空
    private static boolean isObjNull(Object object){
        // 利用反射检查所有字段是否为空，若全为空则不添加
        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            try {
                Object valu = field.get(object);
                if(valu != null){
                    // 任意字段不为空则实体类不为空
                    return true;
                }
            }catch (IllegalAccessException e){
                e.printStackTrace();
            }
        }
        return false;
    }

    /** TODO 此方法为根据excel表动态创建表到数据库，并将excel表内容动态添加进表，若表已存在则会创建失败
     * 实现读取指定行不为空的的表格
     * 返回类型为List<TableColumn>,将excel表格与数据库表对应，类型根据表格下面不为空的类型确定，若都为空则类型为VARCHAR
     * */
    public static Map<String,Object> getTableColumnsXLSX(XSSFWorkbook excel) throws Exception {
        // 获取设置字段名
        Map<Integer, Map<String, List<String>>> mapMap = setValuesXLSX(excel,-1,-1);

        Map<String,Object> map1 = new HashMap<>();

        Map<Integer,List<TableColumn>> map = new HashMap<>();
        // 设置字段类型
        // 确定从第几行开始循环往下确定数据类型
        Set<Integer> integers = mapMap.keySet();

        Map<Integer,List<List<Map<String,Object>>>> map2 = new HashMap<>();

        for (Integer integer : integers) {
            List<List<Map<String,Object>>> listList = new ArrayList<>();

            Map<String, List<String>> stringListMap = mapMap.get(integer);
            Set<String> strings = stringListMap.keySet();
            for (String string : strings) { //此循环实际上肯定只有一个
                List<String> list = stringListMap.get(string);
                //依次取出列名，对出现重复名称的列名做处理
                for (int i = 0; i < list.size(); i++) {
                    for (int j = i + 1; j < list.size(); j++) {
                        if(list.get(i).equals(list.get(j))){//依次和后面数据比较，若出现相同名，则修改
                            String s = list.get(j);
                            String new_name = s + "_"+ j; //将该重名项后添加对应列数信息，防止重名
                            list.set(j, new_name);
                        }
                    }
                }

                List<String> list1 = setValuesTypeXLSX(excel, integer, string, list.size());

                // 开始设置表格
                List<TableColumn> columns = new ArrayList<TableColumn>();
                List<TableColumn> tableColumns = staticMap.get(integer);
                for (int i = 0; i < list.size(); i++) {
                    TableColumn column = new TableColumn();
                    //设置表名
                    column.setName(list.get(i));
                    //设置表备注
                    TableColumn tableColumn = tableColumns.get(i);
                    column.setComment(tableColumn.getComment());
                    column.setOnly(tableColumn.getOnly());

                    column.setType(list1.get(i));
                    if(list1.get(i).equalsIgnoreCase("varchar")){
                        column.setLength(100);
                    } else if (list1.get(i).equalsIgnoreCase("decimal")) {
                        column.setLength(20);
                    }
                    columns.add(column);
                }
                map.put(integer, columns);
                map1.put("table",map);

                // 设置数据集合
                XSSFSheet sheet = excel.getSheetAt(integer);
                //行数
                int lastRowNum = sheet.getLastRowNum();

                List<Map<String,Object>> tableInserts = new ArrayList<>();
                //记录一行数据次数（合并行算一行）
                int p = 0;
                //从确定表字段名的下一行开始取数据
                for (int i = Integer.parseInt(string) + 1; i <= lastRowNum; i++) {
                    Integer valueNum = -1;
                    //下一行合并了多少行
                    int mergedRows = getLhXlsx(sheet, Integer.parseInt(string));
                    XSSFRow row = sheet.getRow(i);
                    if(mergedRows > 0){
                        //合并行只取一次行数据
                        i = i + mergedRows - 1;
                    }

                    Map<String,Object> objectMap = new HashMap<>();

                    int t = 0;
                    for (int j = 0; j < list.size(); j++) { //一共的列数等于设置数据库表的列数-合并列
                        boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), j);
                        //表名列是合并列跳过，在后面处理
                        if(mergedCell){
                            continue;
                        }
                        XSSFCell cell = row.getCell(j);
                        if(cell == null){
                            continue;
                        }
                        String stringCellValue;
                        if(cell != null){
                            switch (cell.getCellType()) {
                                case STRING:
                                    stringCellValue = cell.getStringCellValue();
                                    valueNum++;
                                    break;
                                case NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        stringCellValue = DateUtils.parseDate2String(cell.getDateCellValue());
                                        valueNum++;
                                    } else {
                                        // 数字以原格式输出，避免添加0
                                        DataFormatter formatter = new DataFormatter();
                                        stringCellValue = formatter.formatCellValue(cell);
                                        // 原格式输出后若千位加逗号，去除逗号
                                        stringCellValue = stringCellValue.replace(",","");
                                        valueNum++;
                                    }
                                    break;
                                case BOOLEAN:
                                    stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                    valueNum++;
                                    break;
                                case FORMULA:
                                    //获取公式计算后的值
                                    XSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                    CellValue evaluate = evaluator.evaluate(cell);
                                    switch (evaluate.getCellType()) {
                                        case NUMERIC:
                                            stringCellValue = String.valueOf(evaluate.getNumberValue());
                                            valueNum++;
                                            break;
                                        case STRING:
                                            stringCellValue = evaluate.getStringValue();
                                            valueNum++;
                                            break;
                                        case BOOLEAN:
                                            stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                            valueNum++;
                                            break;
                                        case ERROR:
                                            System.out.println("值: 错误");
                                            stringCellValue = "";
                                            valueNum++;
                                            break;
                                        default:
                                            System.out.println("值: 未知类型");
                                            stringCellValue = "";
                                            valueNum++;
                                    }
                                    break;
                                default:
                                    stringCellValue = "";
                                    valueNum++;
                            }
                        }else {
                            continue;
                        }
                        //每一列的数据对应
                        objectMap.put(list.get(t),stringCellValue);
                        t = t +1;
                    }
                    //合并行加数据
                    System.out.println("合并添加数据,第"+p+"次");
                    int inNum = 0;

                    //从合并行的下一行开始取多余合并数据
                    for (int l = 0; l < list.size(); l++) {
                        boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), l);
                        if(mergedCell){
                            //合并列跳过
                            continue;
                        }
                        int q = Integer.parseInt(string) + mergedRows+1;
                        boolean zs = true;
                        for (int k = Integer.parseInt(string) + 2; k < q; k++) {
                            if(p > 0 && zs){
                                k = k + p * mergedRows;
                                q = q + p * mergedRows;
                                zs = false;
                            }
                            boolean mergedCell1 = isMergedCell(sheet, k, l);
                            if(mergedCell1){
                                //合并行跳过-合并行数据已经取过
                                continue;
                            }
                            XSSFRow row1 = sheet.getRow(k);
                            XSSFCell cell = row1.getCell(l);
                            String stringCellValue = null;
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                    case STRING:
                                        stringCellValue = cell.getStringCellValue();
                                        break;
                                    case NUMERIC:
                                        if (DateUtil.isCellDateFormatted(cell)) {
                                            stringCellValue = DateUtils.parseDate2String(cell.getDateCellValue());
                                        } else {
                                            // 数字以原格式输出，避免添加0
                                            DataFormatter formatter = new DataFormatter();
                                            stringCellValue = formatter.formatCellValue(cell);
                                            // 原格式输出后若千位加逗号，去除逗号
                                            stringCellValue = stringCellValue.replace(",", "");
                                        }
                                        break;
                                    case BOOLEAN:
                                        stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                        break;
                                    case FORMULA:
                                        //获取公式计算后的值
                                        XSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                        CellValue evaluate = evaluator.evaluate(cell);
                                        switch (evaluate.getCellType()) {
                                            case NUMERIC:
                                                stringCellValue = String.valueOf(evaluate.getNumberValue());
                                                break;
                                            case STRING:
                                                stringCellValue = evaluate.getStringValue();
                                                break;
                                            case BOOLEAN:
                                                stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                                break;
                                            case ERROR:
                                                System.out.println("值: 错误");
                                                stringCellValue = "";
                                                break;
                                            default:
                                                System.out.println("值: 未知类型");
                                                stringCellValue = "";
                                        }
                                        break;
                                    default:
                                        stringCellValue = "";
                                        break;
                                }
                            } else {
                                //没有数据跳过
                                continue;
                            }
                            if (inNum == 0) {
                                int lh = getLhXlsx(sheet, Integer.valueOf(string));
                                int ll = getLLXlsx(sheet, Integer.valueOf(string));
                                valueNum = list.size() - (lh * ll) - (lh - 1);
                            } else {
                                valueNum = valueNum + 1;
                            }
                            objectMap.put(list.get(valueNum), stringCellValue);
                            inNum = 1;
                        }
                    }
                    //合并列加数据
                    if(addRowNum > 0){
                        //依次取列数据
                        for (int k = 0; k < list.size(); k++) {
                            boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), k);
                            if(!mergedCell){
                                continue;
                            }
                            int q = lastRowNum + 1;
                            boolean zs = true;
                            for (int j = Integer.parseInt(string) + 1; j < q; j++) {
                                if(p > 0 && zs){
                                    j = j + p * mergedRows;
                                    q = q + p * mergedRows;
                                    zs = false;
                                }
                                XSSFRow row1 = sheet.getRow(j);
                                if(row1 == null){
                                    continue;
                                }
                                XSSFCell cell = row1.getCell(k);
                                String stringCellValue = null;
                                if (cell != null) {
                                    switch (cell.getCellType()) {
                                        case STRING:
                                            stringCellValue = cell.getStringCellValue();
                                            break;
                                        case NUMERIC:
                                            if (DateUtil.isCellDateFormatted(cell)) {
                                                stringCellValue = DateUtils.parseDate2String(cell.getDateCellValue());
                                            } else {
                                                // 数字以原格式输出，避免添加0
                                                DataFormatter formatter = new DataFormatter();
                                                stringCellValue = formatter.formatCellValue(cell);
                                                // 原格式输出后若千位加逗号，去除逗号
                                                stringCellValue = stringCellValue.replace(",", "");
                                            }
                                            break;
                                        case BOOLEAN:
                                            stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                            break;
                                        case FORMULA:
                                            //获取公式计算后的值
                                            XSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                            CellValue evaluate = evaluator.evaluate(cell);
                                            switch (evaluate.getCellType()) {
                                                case NUMERIC:
                                                    stringCellValue = String.valueOf(evaluate.getNumberValue());
                                                    break;
                                                case STRING:
                                                    stringCellValue = evaluate.getStringValue();
                                                    break;
                                                case BOOLEAN:
                                                    stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                                    break;
                                                case ERROR:
                                                    System.out.println("值: 错误");
                                                    stringCellValue = "";
                                                    break;
                                                default:
                                                    System.out.println("值: 未知类型");
                                                    stringCellValue = "";
                                            }
                                            break;
                                        default:
                                            stringCellValue = "";
                                            break;
                                    }
                                } else {
                                    //没有数据跳过
                                    continue;
                                }
                                valueNum = valueNum+1;
                                objectMap.put(list.get(valueNum), stringCellValue);
                            }
                        }
                    }
                    // 每一行的数据
                    tableInserts.add(objectMap);
                    p = p + 1;
                }
                // 每张表的数据
                listList.add(tableInserts);
            }
            map2.put(integer,listList);
        }
        map1.put("tableIn",map2);

        // 清除数据
        staticMap.clear();
        //重置合并行列数
        addCelNum = 0;
        addRowNum = 0;
        hbhNum = 0;
        hblNum = 0;
        return map1;
    }

    public static Map<String,Object> getTableColumnsXLS(HSSFWorkbook excel) throws Exception {
        // 获取设置字段名
        Map<Integer, Map<String, List<String>>> mapMap = setValuesXLS(excel);

        Map<String,Object> map1 = new HashMap<>();

        Map<Integer,List<TableColumn>> map = new HashMap<>();
        // 设置字段类型
        // 确定从第几行开始循环往下确定数据类型
        Set<Integer> integers = mapMap.keySet();

        Map<Integer,List<List<Map<String,Object>>>> map2 = new HashMap<>();

        for (Integer integer : integers) {
            List<List<Map<String,Object>>> listList = new ArrayList<>();

            Map<String, List<String>> stringListMap = mapMap.get(integer);
            Set<String> strings = stringListMap.keySet();
            for (String string : strings) { //此循环实际上肯定只有一个
                List<String> list = stringListMap.get(string);
                //依次取出列名，对出现重复名称的列名做处理
                for (int i = 0; i < list.size(); i++) {
                    for (int j = i + 1; j < list.size(); j++) {
                        if(list.get(i).equals(list.get(j))){//依次和后面数据比较，若出现相同名，则修改
                            String s = list.get(j);
                            String new_name = s + "_"+ j; //将该重名项后添加对应列数信息，防止重名
                            list.set(j, new_name);
                        }
                    }
                }

                List<String> list1 = setValuesTypeXLS(excel, integer, string, list.size());

                // 开始设置表格
                List<TableColumn> columns = new ArrayList<TableColumn>();
                List<TableColumn> tableColumns = staticMap.get(integer);
                for (int i = 0; i < list.size(); i++) {
                    TableColumn column = new TableColumn();
                    //设置表名
                    column.setName(list.get(i));
                    //设置表备注
                    TableColumn tableColumn = tableColumns.get(i);
                    column.setComment(tableColumn.getComment());
                    column.setOnly(tableColumn.getOnly());

                    column.setType(list1.get(i));
                    if(list1.get(i).equalsIgnoreCase("varchar")){
                        column.setLength(100);
                    } else if (list1.get(i).equalsIgnoreCase("decimal")) {
                        column.setLength(20);
                    }
                    columns.add(column);
                }
                map.put(integer, columns);
                map1.put("table",map);

                // 设置数据集合
                HSSFSheet sheet = excel.getSheetAt(integer);
                //行数
                int lastRowNum = sheet.getLastRowNum();

                List<Map<String,Object>> tableInserts = new ArrayList<>();
                //记录一行数据次数（合并行算一行）
                int p = 0;
                //从确定表字段名的下一行开始取数据
                for (int i = Integer.parseInt(string) + 1; i <= lastRowNum; i++) {
                    Integer valueNum = -1;
                    //下一行第一列确定合并了多少行
                    int mergedRows = getLhXls(sheet, Integer.parseInt(string));
                    HSSFRow row = sheet.getRow(i);
                    if(mergedRows > 0){
                        //合并行只取一次行数据
                        i = i + mergedRows - 1;
                    }

                    Map<String,Object> objectMap = new HashMap<>();

                    int t = 0;
                    for (int j = 0; j < list.size(); j++) { //一共的列数等于设置数据库表的列数
                        boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), j);
                        //表名列是合并列跳过，在后面处理
                        if(mergedCell){
                            continue;
                        }
                        HSSFCell cell = row.getCell(j);
                        if(cell == null){
                            continue;
                        }
                        String stringCellValue;
                        if(cell != null){
                            switch (cell.getCellType()) {
                                case STRING:
                                    stringCellValue = cell.getStringCellValue();
                                    valueNum++;
                                    break;
                                case NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        stringCellValue = DateUtils.parseDate2String(cell.getDateCellValue());
                                        valueNum++;
                                    } else {
                                        // 数字以原格式输出，避免添加0
                                        DataFormatter formatter = new DataFormatter();
                                        stringCellValue = formatter.formatCellValue(cell);
                                        // 原格式输出后若千位加逗号，去除逗号
                                        stringCellValue = stringCellValue.replace(",","");
                                        valueNum++;
                                    }
                                    break;
                                case BOOLEAN:
                                    stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                    valueNum++;
                                    break;
                                case FORMULA:
                                    //获取公式计算后的值
                                    HSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                    CellValue evaluate = evaluator.evaluate(cell);
                                    switch (evaluate.getCellType()) {
                                        case NUMERIC:
                                            stringCellValue = String.valueOf(evaluate.getNumberValue());
                                            valueNum++;
                                            break;
                                        case STRING:
                                            stringCellValue = evaluate.getStringValue();
                                            valueNum++;
                                            break;
                                        case BOOLEAN:
                                            stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                            valueNum++;
                                            break;
                                        case ERROR:
                                            System.out.println("值: 错误");
                                            stringCellValue = "";
                                            valueNum++;
                                            break;
                                        default:
                                            System.out.println("值: 未知类型");
                                            stringCellValue = "";
                                            valueNum++;
                                    }
                                    break;
                                default:
                                    stringCellValue = "";
                                    valueNum++;
                            }
                        }else {
                            continue;
                        }
                        //每一列的数据对应
                        objectMap.put(list.get(j),stringCellValue);
                        t = t +1;
                    }
                    //合并行加数据
                    System.out.println("合并添加数据,第"+p+"次");
                    int inNum = 0;

                    //从合并行的下一行开始取多余合并数据
                    for (int l = 0; l < list.size(); l++) {
                        boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), l);
                        if(mergedCell){
                            //合并列跳过
                            continue;
                        }
                        int q = Integer.parseInt(string) + mergedRows+1;
                        boolean zs = true;
                        for (int k = Integer.parseInt(string) + 2; k < q; k++) {
                            if(p > 0 && zs){
                                k = k + p * mergedRows;
                                q = q + p * mergedRows;
                                zs = false;
                            }
                            boolean mergedCell1 = isMergedCell(sheet, k, l);
                            if(mergedCell1){
                                //合并行跳过-合并行数据已经取过
                                continue;
                            }
                            HSSFRow row1 = sheet.getRow(k);
                            HSSFCell cell = row1.getCell(l);
                            String stringCellValue = null;
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                    case STRING:
                                        stringCellValue = cell.getStringCellValue();
                                        break;
                                    case NUMERIC:
                                        if (DateUtil.isCellDateFormatted(cell)) {
                                            stringCellValue = DateUtils.parseDate2String(cell.getDateCellValue());
                                        } else {
                                            // 数字以原格式输出，避免添加0
                                            DataFormatter formatter = new DataFormatter();
                                            stringCellValue = formatter.formatCellValue(cell);
                                            // 原格式输出后若千位加逗号，去除逗号
                                            stringCellValue = stringCellValue.replace(",", "");
                                        }
                                        break;
                                    case BOOLEAN:
                                        stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                        break;
                                    case FORMULA:
                                        //获取公式计算后的值
                                        HSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                        CellValue evaluate = evaluator.evaluate(cell);
                                        switch (evaluate.getCellType()) {
                                            case NUMERIC:
                                                stringCellValue = String.valueOf(evaluate.getNumberValue());
                                                break;
                                            case STRING:
                                                stringCellValue = evaluate.getStringValue();
                                                break;
                                            case BOOLEAN:
                                                stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                                break;
                                            case ERROR:
                                                System.out.println("值: 错误");
                                                stringCellValue = "";
                                                break;
                                            default:
                                                System.out.println("值: 未知类型");
                                                stringCellValue = "";
                                        }
                                        break;
                                    default:
                                        stringCellValue = "";
                                        break;
                                }
                            } else {
                                //没有数据跳过
                                continue;
                            }
                            if (inNum == 0) {
                                int lh = getLhXls(sheet, Integer.valueOf(string));
                                int ll = getLLXls(sheet, Integer.valueOf(string));
                                valueNum = list.size() - (lh * ll) - (lh - 1);
                            } else {
                                valueNum = valueNum + 1;
                            }
                            objectMap.put(list.get(valueNum), stringCellValue);
                            inNum = 1;
                        }
                    }
                    //合并列加数据
                    if(addRowNum > 0){
                        //依次取列数据
                        for (int k = 0; k < list.size(); k++) {
                            boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), k);
                            if(!mergedCell){
                                continue;
                            }
                            int q = lastRowNum + 1;
                            boolean zs = true;
                            for (int j = Integer.parseInt(string) + 1; j < q; j++) {
                                if(p > 0 && zs){
                                    j = j + p * mergedRows;
                                    q = q + p * mergedRows;
                                    zs = false;
                                }
                                HSSFRow row1 = sheet.getRow(j);
                                if(row1 == null){
                                    continue;
                                }
                                HSSFCell cell = row1.getCell(k);
                                String stringCellValue = null;
                                if (cell != null) {
                                    switch (cell.getCellType()) {
                                        case STRING:
                                            stringCellValue = cell.getStringCellValue();
                                            break;
                                        case NUMERIC:
                                            if (DateUtil.isCellDateFormatted(cell)) {
                                                stringCellValue = DateUtils.parseDate2String(cell.getDateCellValue());
                                            } else {
                                                // 数字以原格式输出，避免添加0
                                                DataFormatter formatter = new DataFormatter();
                                                stringCellValue = formatter.formatCellValue(cell);
                                                // 原格式输出后若千位加逗号，去除逗号
                                                stringCellValue = stringCellValue.replace(",", "");
                                            }
                                            break;
                                        case BOOLEAN:
                                            stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                            break;
                                        case FORMULA:
                                            //获取公式计算后的值
                                            HSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                            CellValue evaluate = evaluator.evaluate(cell);
                                            switch (evaluate.getCellType()) {
                                                case NUMERIC:
                                                    stringCellValue = String.valueOf(evaluate.getNumberValue());
                                                    break;
                                                case STRING:
                                                    stringCellValue = evaluate.getStringValue();
                                                    break;
                                                case BOOLEAN:
                                                    stringCellValue = String.valueOf(evaluate.getBooleanValue());
                                                    break;
                                                case ERROR:
                                                    System.out.println("值: 错误");
                                                    stringCellValue = "";
                                                    break;
                                                default:
                                                    System.out.println("值: 未知类型");
                                                    stringCellValue = "";
                                            }
                                            break;
                                        default:
                                            stringCellValue = "";
                                            break;
                                    }
                                } else {
                                    //没有数据跳过
                                    continue;
                                }
                                valueNum = valueNum+1;
                                objectMap.put(list.get(valueNum), stringCellValue);
                            }
                        }
                    }
                    // 每一行的数据
                    tableInserts.add(objectMap);
                    p = p + 1;
                }
                // 每张表的数据
                listList.add(tableInserts);
            }
            map2.put(integer,listList);
        }
        map1.put("tableIn",map2);

        // 清除数据
        staticMap.clear();
        //重置合并行列数
        addCelNum = 0;
        addRowNum = 0;
        hbhNum = 0;
        hblNum = 0;
        return map1;
    }

    // 设置每列的数据类型
    private static List<String> setValuesTypeXLSX(XSSFWorkbook excel, Integer integer, String string, Integer lc) {
        //表字段类型集合
        List<String> values = new ArrayList<>();
        // 实际此key只有一个，确定的某一行
        XSSFSheet sheet = excel.getSheetAt(integer);
        //行数
        int lastRowNum = sheet.getLastRowNum();

        // 列循环次数
        for (int i = 0; i < lc; i++){
            String stringCellValue = "";
            boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), i);
            //合并列跳过
            if(mergedCell){
                continue;
            }
            // 记录该类型被赋予次数
//            int num = 0;
            //是否是可设置的列
            boolean isE = false;
            // 以该行的下一行开始获取数据类型
            for (int j = Integer.parseInt(string) + 1; j <= lastRowNum; j++) {
                XSSFRow row = sheet.getRow(j);
                XSSFCell cell = row.getCell(i);
                if (cell != null) {
                    isE = true;
                    switch (cell.getCellType()) {
                        case STRING:
                            // 字符串类型
                            stringCellValue = "VARCHAR";
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                // 日期类型
                                stringCellValue = "datetime";
                            } else {
                                // 数字类型
                                stringCellValue = "decimal";
                            }
                            break;
                        case BOOLEAN:
                            // 也存储为字符串
                            stringCellValue = "VARCHAR";
                            break;
                        case FORMULA:
                            //获取公式计算后的值
                            XSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                            CellValue evaluate = evaluator.evaluate(cell);
                            switch (evaluate.getCellType()) {
                                case NUMERIC:
                                    stringCellValue = "decimal";
                                    break;
                                case STRING:
                                    stringCellValue = "VARCHAR";
                                    break;
                                case BOOLEAN:
                                    stringCellValue = "VARCHAR";
                                    break;
                                case ERROR:
                                    System.out.println("值: 错误");
                                    stringCellValue = "VARCHAR";
                                    break;
                                default:
                                    System.out.println("值: 未知类型");
                                    stringCellValue = "VARCHAR";
                            }
                            break;
                        default:
                            // 都不是的类型，或者此处数据为空，继续找该列下一行数据，直到找完所有行
                            continue;
                    }

//                    if(num >=2){ //两次被设置为同一类型，
//
//                    }
                }
                if(!StringUtils.isAllBlank(stringCellValue)){ //已经找到有数据的行，则直接以此数据类型设置
                    values.add(stringCellValue);
                    // 结束此列数据类型设置
                    break;
                }
                if(cell == null){
                    isE = false;
                }

            }

            if(StringUtils.isEmpty(stringCellValue) && isE){ //该列数据每一行都是空，没有数据，则默认设置为字符串
                values.add("VARCHAR");
            }

        }
        //合并行数-以数据行第一行合并最多的列确定合并行数
        int lh = getLhXlsx(sheet, Integer.parseInt(string));
        //合并行加数据类型
        for (int i = 0; i < lc; i++) {
            //是否是可设置的列
            boolean isE = false;
            boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), i);
            //合并列跳过
            if(mergedCell){
                continue;
            }

            for (int j = 0; j < lh; j++) {
                String stringCellValue = "";
                boolean mergedCell1 = isMergedCell(sheet, Integer.parseInt(string) + j + 1, i);
                if(!mergedCell1){
                    if(j > 0){
                        //从第二次开始增加数据类型
                        XSSFRow row = sheet.getRow(Integer.parseInt(string) + j + 1);
                        XSSFCell cell = row.getCell(i);
                        if (cell != null) {
                            isE = true;
                            switch (cell.getCellType()) {
                                case STRING:
                                    // 字符串类型
                                    stringCellValue = "VARCHAR";
//                            num ++;
                                    break;
                                case NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        // 日期类型
                                        stringCellValue = "datetime";
                                    } else {
                                        // 数字类型
                                        stringCellValue = "decimal";
                                    }
//                            num ++;
                                    break;
                                case BOOLEAN:
                                    // 也存储为字符串
                                    stringCellValue = "VARCHAR";
//                            num ++;
                                    break;
                                case FORMULA:
                                    //获取公式计算后的值
                                    XSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                    CellValue evaluate = evaluator.evaluate(cell);
                                    switch (evaluate.getCellType()) {
                                        case NUMERIC:
                                            stringCellValue = "decimal";
                                            break;
                                        case STRING:
                                            stringCellValue = "VARCHAR";
                                            break;
                                        case BOOLEAN:
                                            stringCellValue = "VARCHAR";
                                            break;
                                        case ERROR:
                                            System.out.println("值: 错误");
                                            stringCellValue = "VARCHAR";
                                            break;
                                        default:
                                            System.out.println("值: 未知类型");
                                            stringCellValue = "VARCHAR";
                                    }
//                            num ++;
                                    break;
                                default:
                                    // 都不是的类型，或者此处数据为空，默认为字符串
                                    stringCellValue = "VARCHAR";
                                    break;
                            }

//                    if(num >=2){ //两次被设置为同一类型，
//
//                    }
                        }else {
                            isE = false;
                        }
                        if(!StringUtils.isAllBlank(stringCellValue)){
                            values.add(stringCellValue);
                        }

                        if(StringUtils.isEmpty(stringCellValue) && isE){
                            values.add("VARCHAR");
                        }

                    }
                }
            }


        }
        //合并列加数据类型
        for (int i = 0; i < lc; i++) {
            boolean isE = false;
            String stringCellValue = "";
            boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), i);
            //非合并列跳过
            if(!mergedCell){
                continue;
            }
            for (int j = Integer.parseInt(string)+1; j < lc; j++) {
                XSSFRow row = sheet.getRow(j);
                if(row == null){
                    continue;
                }
                XSSFCell cell = row.getCell(i);
                if (cell != null) {
                    isE = true;
                    switch (cell.getCellType()) {
                        case STRING:
                            // 字符串类型
                            stringCellValue = "VARCHAR";
//                            num ++;
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                // 日期类型
                                stringCellValue = "datetime";
                            } else {
                                // 数字类型
                                stringCellValue = "decimal";
                            }
//                            num ++;
                            break;
                        case BOOLEAN:
                            // 也存储为字符串
                            stringCellValue = "VARCHAR";
//                            num ++;
                            break;
                        case FORMULA:
                            //获取公式计算后的值
                            XSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                            CellValue evaluate = evaluator.evaluate(cell);
                            switch (evaluate.getCellType()) {
                                case NUMERIC:
                                    stringCellValue = "decimal";
                                    break;
                                case STRING:
                                    stringCellValue = "VARCHAR";
                                    break;
                                case BOOLEAN:
                                    stringCellValue = "VARCHAR";
                                    break;
                                case ERROR:
                                    System.out.println("值: 错误");
                                    stringCellValue = "VARCHAR";
                                    break;
                                default:
                                    System.out.println("值: 未知类型");
                                    stringCellValue = "VARCHAR";
                            }
//                            num ++;
                            break;
                        default:
                            // 都不是的类型，或者此处数据为空，直接默认为字符串
                            stringCellValue = "VARCHAR";
                            break;
                    }

//                    if(num >=2){ //两次被设置为同一类型，
//
//                    }
                }else {
                    isE = false;
                }
                if(!StringUtils.isAllBlank(stringCellValue)){
                    values.add(stringCellValue);
                }
                if(StringUtils.isEmpty(stringCellValue) && isE){
                    values.add("VARCHAR");
                }
            }
        }
        return values;
    }

    // 设置每列的数据类型
    private static List<String> setValuesTypeXLS(HSSFWorkbook excel, Integer integer, String string, Integer lc) {
        //表字段类型集合
        List<String> values = new ArrayList<>();
        // 实际此key只有一个，确定的某一行
        HSSFSheet sheet = excel.getSheetAt(integer);
        //行数
        int lastRowNum = sheet.getLastRowNum();

        // 列循环次数
        for (int i = 0; i < lc; i++){
            String stringCellValue = "";
            boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), i);
            //合并列跳过
            if(mergedCell){
                continue;
            }
            // 记录该类型被赋予次数
//            int num = 0;
            //是否是可设置的列
            boolean isE = false;
            // 以该行的下一行开始获取数据类型
            for (int j = Integer.parseInt(string) + 1; j <= lastRowNum; j++) {
                HSSFRow row = sheet.getRow(j);
                HSSFCell cell = row.getCell(i);
                if (cell != null) {
                    isE = true;
                    switch (cell.getCellType()) {
                        case STRING:
                            // 字符串类型
                            stringCellValue = "VARCHAR";
//                            num ++;
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                // 日期类型
                                stringCellValue = "datetime";
                            } else {
                                // 数字类型
                                stringCellValue = "decimal";
                            }
//                            num ++;
                            break;
                        case BOOLEAN:
                            // 也存储为字符串
                            stringCellValue = "VARCHAR";
//                            num ++;
                            break;
                        case FORMULA:
                            //获取公式计算后的值
                            HSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                            CellValue evaluate = evaluator.evaluate(cell);
                            switch (evaluate.getCellType()) {
                                case NUMERIC:
                                    stringCellValue = "decimal";
                                    break;
                                case STRING:
                                    stringCellValue = "VARCHAR";
                                    break;
                                case BOOLEAN:
                                    stringCellValue = "VARCHAR";
                                    break;
                                case ERROR:
                                    System.out.println("值: 错误");
                                    stringCellValue = "VARCHAR";
                                    break;
                                default:
                                    System.out.println("值: 未知类型");
                                    stringCellValue = "VARCHAR";
                            }
//                            num ++;
                            break;
                        default:
                            // 都不是的类型，或者此处数据为空，继续找该列下一行数据，直到找完所有行
                            continue;
                    }

//                    if(num >=2){ //两次被设置为同一类型，
//
//                    }
                }
                if(!StringUtils.isAllBlank(stringCellValue)){ //已经找到有数据的行，则直接以此数据类型设置
                    values.add(stringCellValue);
                    // 结束此列数据类型设置
                    break;
                }
                if(cell == null){
                    isE = false;
                }

            }

            if(StringUtils.isEmpty(stringCellValue) && isE){ //该列数据每一行都是空，没有数据，则默认设置为字符串
                values.add("VARCHAR");
            }

        }
        //合并行数-以数据行第一行合并最多的列确定合并行数
        int lh = getLhXls(sheet, Integer.parseInt(string));
        //合并行加数据类型
        for (int i = 0; i < lc; i++) {
            //是否是可设置的列
            boolean isE = false;
            boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), i);
            //合并列跳过
            if(mergedCell){
                continue;
            }

            for (int j = 0; j < lh; j++) {
                String stringCellValue = "";
                boolean mergedCell1 = isMergedCell(sheet, Integer.parseInt(string) + j + 1, i);
                if(!mergedCell1){
                    if(j > 0){
                        //从第二次开始增加数据类型
                        HSSFRow row = sheet.getRow(Integer.parseInt(string) + j + 1);
                        HSSFCell cell = row.getCell(i);
                        if (cell != null) {
                            isE = true;
                            switch (cell.getCellType()) {
                                case STRING:
                                    // 字符串类型
                                    stringCellValue = "VARCHAR";
//                            num ++;
                                    break;
                                case NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        // 日期类型
                                        stringCellValue = "datetime";
                                    } else {
                                        // 数字类型
                                        stringCellValue = "decimal";
                                    }
//                            num ++;
                                    break;
                                case BOOLEAN:
                                    // 也存储为字符串
                                    stringCellValue = "VARCHAR";
//                            num ++;
                                    break;
                                case FORMULA:
                                    //获取公式计算后的值
                                    HSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                                    CellValue evaluate = evaluator.evaluate(cell);
                                    switch (evaluate.getCellType()) {
                                        case NUMERIC:
                                            stringCellValue = "decimal";
                                            break;
                                        case STRING:
                                            stringCellValue = "VARCHAR";
                                            break;
                                        case BOOLEAN:
                                            stringCellValue = "VARCHAR";
                                            break;
                                        case ERROR:
                                            System.out.println("值: 错误");
                                            stringCellValue = "VARCHAR";
                                            break;
                                        default:
                                            System.out.println("值: 未知类型");
                                            stringCellValue = "VARCHAR";
                                    }
//                            num ++;
                                    break;
                                default:
                                    // 都不是的类型，或者此处数据为空，默认为字符串
                                    stringCellValue = "VARCHAR";
                                    break;
                            }

//                    if(num >=2){ //两次被设置为同一类型，
//
//                    }
                        }else {
                            isE = false;
                        }
                        if(!StringUtils.isAllBlank(stringCellValue)){
                            values.add(stringCellValue);
                        }

                        if(StringUtils.isEmpty(stringCellValue) && isE){
                            values.add("VARCHAR");
                        }

                    }
                }
            }


        }
        //合并列加数据类型
        for (int i = 0; i < lc; i++) {
            boolean isE = false;
            String stringCellValue = "";
            boolean mergedCell = isMergedCell(sheet, Integer.parseInt(string), i);
            //非合并列跳过
            if(!mergedCell){
                continue;
            }
            for (int j = Integer.parseInt(string)+1; j < lc; j++) {
                HSSFRow row = sheet.getRow(j);
                if(row == null){
                    continue;
                }
                HSSFCell cell = row.getCell(i);
                if (cell != null) {
                    isE = true;
                    switch (cell.getCellType()) {
                        case STRING:
                            // 字符串类型
                            stringCellValue = "VARCHAR";
//                            num ++;
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                // 日期类型
                                stringCellValue = "datetime";
                            } else {
                                // 数字类型
                                stringCellValue = "decimal";
                            }
//                            num ++;
                            break;
                        case BOOLEAN:
                            // 也存储为字符串
                            stringCellValue = "VARCHAR";
//                            num ++;
                            break;
                        case FORMULA:
                            //获取公式计算后的值
                            HSSFFormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
                            CellValue evaluate = evaluator.evaluate(cell);
                            switch (evaluate.getCellType()) {
                                case NUMERIC:
                                    stringCellValue = "decimal";
                                    break;
                                case STRING:
                                    stringCellValue = "VARCHAR";
                                    break;
                                case BOOLEAN:
                                    stringCellValue = "VARCHAR";
                                    break;
                                case ERROR:
                                    System.out.println("值: 错误");
                                    stringCellValue = "VARCHAR";
                                    break;
                                default:
                                    System.out.println("值: 未知类型");
                                    stringCellValue = "VARCHAR";
                            }
//                            num ++;
                            break;
                        default:
                            // 都不是的类型，或者此处数据为空，直接默认为字符串
                            stringCellValue = "VARCHAR";
                            break;
                    }

//                    if(num >=2){ //两次被设置为同一类型，
//
//                    }
                }else {
                    isE = false;
                }
                if(!StringUtils.isAllBlank(stringCellValue)){
                    values.add(stringCellValue);
                }
                if(StringUtils.isEmpty(stringCellValue) && isE){
                    values.add("VARCHAR");
                }
            }
        }
        return values;
    }

    //返回对应单元格是否具有唯一性的方法
    public static Boolean isOnlyXlsx(XSSFCell cell){
        XSSFCellStyle xssfStyle = cell.getCellStyle();
        XSSFColor color = xssfStyle.getFillForegroundColorColor();
        if (color != null) {
            byte[] rgb = color.getRGB(); // 获取 RGB 字节数组
            if (rgb != null) {
                System.out.println("rgb值"+rgb[0]+"==="+rgb[1]+"======"+rgb[2]);
                if((rgb[0] == -64 || rgb[0] == 192) && rgb[1] == 0 && rgb[2] == 0){
                    return true;
                }
                return false;
            } else {
                return false;
            }
        }
        return false;
    }
    //是否需要比较
    public static Boolean isCompareXlsx(XSSFCell cell){
        XSSFCellStyle xssfStyle = cell.getCellStyle();
        XSSFColor color = xssfStyle.getFillForegroundColorColor();
        if (color != null) {
            byte[] rgb = color.getRGB(); // 获取 RGB 字节数组
            if (rgb != null) {
                System.out.println("rgb值"+rgb[0]+"==="+rgb[1]+"======"+rgb[2]);
                if(rgb[0] == 0 && rgb[1] == -80 && rgb[2] == -16){
                    return true;
                }
                return false;
            } else {
                return false;
            }
        }
        return false;
    }
    public static Boolean isCompareXls(HSSFCell cell){
        HSSFWorkbook excel1 = new HSSFWorkbook();
        CellStyle style = cell.getCellStyle();
        if (style.getFillPattern() == FillPatternType.NO_FILL) {
            //无填充色
            return false;
        }
        HSSFPalette palette = excel1.getCustomPalette();
        // 4. 获取前景色（HSSF中填充色通常存储在前景色）
        short foregroundColorIndex = style.getFillForegroundColor();
        HSSFColor foregroundColor = palette.getColor(foregroundColorIndex);
        if (foregroundColor != null) {
            short[] rgb = foregroundColor.getTriplet();
            if(rgb[0] == 0 && rgb[1] == 204 && rgb[2] == 255){
                return true;
            }
            return false;
        }
        return false;
    }
    public static Boolean isOnlyXls(HSSFCell cell){
        HSSFWorkbook excel1 = new HSSFWorkbook();
        CellStyle style = cell.getCellStyle();
        if (style.getFillPattern() == FillPatternType.NO_FILL) {
            //无填充色
            return false;
        }
        HSSFPalette palette = excel1.getCustomPalette();
        // 4. 获取前景色（HSSF中填充色通常存储在前景色）
        short foregroundColorIndex = style.getFillForegroundColor();
        HSSFColor foregroundColor = palette.getColor(foregroundColorIndex);
        if (foregroundColor != null) {
            short[] rgb = foregroundColor.getTriplet();
            if(rgb[0] == 255 && rgb[1] == 0 && rgb[2] == 0){
                return true;
            }
            return false;
        }
        return false;
    }

    /**
     * 设置字段名
     * 返回为：第一层，第几张表，第二层，第几行的数据确定的表字段名
     * */
    public static Map<Integer,Map<String, List<String>>> setValuesXLSX(XSSFWorkbook excel,Integer sl,Integer ty) {
        Map<Integer,Map<String, List<String>>> mapMap = new HashMap<>();
        int sheets = excel.getNumberOfSheets();

        for (int i = 0; i < sheets; i++) {
            XSSFSheet sheet = excel.getSheetAt(i);
            // 字段名集合
            List<String> res_list = new ArrayList<>();
            // 最终取的行
            Integer res_h = 0;
            Map<String,List<String>> map = new HashMap<>();

            //行数
            int lastRowNum = sheet.getLastRowNum() + 1;

            List<String> temp = List.of();
            //是否具有唯一性
            List<Boolean> isOnly = List.of();
            //以第一次出现的最长不为空的行为基础创建表
            int listLength = 0;
            int lastLength = 0;
            if(sl != null && sl >= 0){
                res_h = sl;
                XSSFRow row = sheet.getRow(sl);
                int lastCellNum = row.getLastCellNum();
                for (int j = 0; j < lastCellNum; j++) {
                    XSSFCell cell = row.getCell(j);
                    if(cell == null){
                        continue;
                    }
                    DataFormatter formatter = new DataFormatter();
                    String stringCellValue = formatter.formatCellValue(cell);
                    if(StringUtils.isEmpty(stringCellValue)){
                        continue;
                    }
                    if(ty != null && ty == 1){
                        Boolean compareXlsx = isCompareXlsx(cell);
                        if(compareXlsx){
                            temp.add(stringCellValue);
                        }
                    }
                    Boolean onlyXlsx = isOnlyXlsx(cell);
                    isOnly.add(onlyXlsx);
                }
            }else {
                for (int j = 0; j < lastRowNum; j++) {
                    List<String> list = new ArrayList<>();
                    List<Boolean> isOnlyIn = new ArrayList<>();

                    XSSFRow row1 = sheet.getRow(j);
                    if (row1 != null) {
                        // 该行列数
                        int lastCellNum = row1.getLastCellNum();
                        for (int k = 0; k < lastCellNum; k++) {
                            boolean mergedCell = isMergedCell(sheet, j, k);
                            if (mergedCell) {
                                continue;
                            }
                            XSSFCell cell = row1.getCell(k);
                            // 保持原始内容
                            DataFormatter formatter = new DataFormatter();
                            String stringCellValue = formatter.formatCellValue(cell);
                            if (!StringUtils.isEmpty(stringCellValue)) {
                                list.add(stringCellValue);
                                Boolean onlyXlsx = isOnlyXlsx(cell);
                                isOnlyIn.add(onlyXlsx);
                            }
                        }
                        listLength = list.size();
                    }
                    if (row1 != null) {
                        int lastCellNum = row1.getLastCellNum();
                        for (int k = 0; k < lastCellNum; k++) {
                            //计算该列是否是合并列
                            boolean mergedCell = isMergedCell(sheet, j, k);

                            XSSFRow row = sheet.getRow(j);
                            XSSFCell cell = row.getCell(k);
                            DataFormatter formatter = new DataFormatter();
                            String stringCellValue = formatter.formatCellValue(cell);
                            if (StringUtils.isEmpty(stringCellValue)) {
                                //为空数据，则不加列数-包括合并数据，合并表格只要有数据，都会添加对应的列数（此处是为防止单元格后面加空数据影响判断数据）
                                continue;
                            }
                            if (mergedCell) {
                                //是合并列，则添加列数
                                CellRangeAddress mergedRegion = getMergedRegion(sheet, j, k);
                                int mergedCols = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;
                                if (mergedCols != 1) {
                                    listLength = listLength + mergedCols;
                                    k = k + mergedCols - 1;
                                } else {
                                    //属于合并行，则只加1
                                    listLength = listLength + 1;
                                }
                            }
                        }
                    }

                    // 比较该行和上一行的长度大小
                    if (j != 0) { //从第二行开始
                        if (lastLength < listLength) {
                            temp = list;
                            // 发生变化时，变成对应的行
                            res_h = j;
                            lastLength = listLength;
                            isOnly = isOnlyIn;
                        }
                    } else {
                        // 存储为已知最长的一行的信息
                        temp = list;
                        lastLength = listLength;
                        res_h = 0; //初始为第一行
                        isOnly = isOnlyIn;
                    }

                }
            }
            XSSFRow row = sheet.getRow(res_h);
            if(row == null){
                //证明是空的表格
                continue;
            }
            int lastCellNum = row.getLastCellNum();
            //计算合并列数
            int mergedCols1 = 0;
            for (int j = 0; j < lastCellNum; j++) {
                boolean mergedCell = isMergedCell(sheet, res_h, j);
                if(mergedCell) {
                    CellRangeAddress mergedRegion = getMergedRegion(sheet, res_h, j);
                    int mergedCols = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;
                    mergedCols1 = mergedCols1 + mergedCols - 1;
                    addRowNum = addRowNum + mergedCols - 1;
                    j = j + mergedCols - 1;
                    hblNum = hblNum + 1;
                }
            }

            //数据行最多合并行列确定合并行数
            int lh = getLhXlsx(sheet, res_h);

            int mergedRows = 0;
            if(lh > 0){
                System.out.println("有合并行数据");
                // 计算合并的行数和列数
                mergedRows = lh;

                hbhNum = mergedRows;
                short lastCellNum1 = row.getLastCellNum();
                int m = 0;
                for (int j = 0; j < lastCellNum1; j++) {
                    //先判断是否是合并列下的数据，合并列则跳过不在此处理
                    boolean mergedCell1 = isMergedCell(sheet, res_h, j);
                    if(mergedCell1){
                        continue;
                    }
                    //有合并行加列数据
                    for (int k = 0; k < mergedRows; k++) {
                        boolean mergedCell = isMergedCell(sheet, res_h + k + 1, j);
                        if(!mergedCell){
                            //合并行后出现的未合并行数据
                            if(k > 0){
                                //从第二个开始增加列数据
                                //在最后加一列数据
                                temp.add(temp.get(m)+"_n"+k);
                                //字段是否具有唯一性也同样加一个，是合并行则类型实际上一样的唯一性
                                isOnly.add(isOnly.get(m));
                            }
                        }
                    }
                    m = m + 1;
                }
                for (int j = 0; j < lastCellNum1; j++) {
                    for (int k = 0; k < mergedRows; k++) {
                        boolean mergedCell = isMergedCell(sheet, res_h + k + 1, j);
                        if(!mergedCell){
                            //合并行后出现的未合并行数据
                            if(k > 0){
                                //从第二个开始增加列数据
                                //合并行列数加一
                                addCelNum = addCelNum + 1;
                            }
                        }
                    }
                }
            }

            int lastCellNum1 = row.getLastCellNum();
            int t = 0;
            int p = 0;
            //合并列加数据
            for (int j = 0; j < lastCellNum1; j++) {
                boolean mergedCell;
                if(t == 0) {
                    mergedCell = isMergedCell(sheet, res_h, j);
                }else {
                    mergedCell = isMergedCell(sheet, res_h, t);
                    p = t;
                    t = t + 1;
                }
                if(mergedCell){
                    if(t == 0) {
                        XSSFRow row1 = sheet.getRow(res_h);
                        XSSFCell cell = row1.getCell(j);
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);
                        if(!StringUtils.isEmpty(stringCellValue)){
                            temp.add(stringCellValue);
                            Boolean onlyXlsx = isOnlyXlsx(cell);
                            isOnly.add(onlyXlsx);
                        }
                        CellRangeAddress mergedRegion1 = getMergedRegion(sheet, res_h, j);
                        int mergedCols2 = mergedRegion1.getLastColumn() - mergedRegion1.getFirstColumn() + 1;
                        int lhXlsx = getLhXlsx(sheet, res_h);
                        int cs;
                        if(lhXlsx == 0){
                            cs = mergedCols2;
                        }else {
                            cs = mergedCols2 * lhXlsx;
                        }

                        int zs = temp.size()-1;
                        for (int k = 1; k < cs; k++) {
                            temp.add(temp.get(zs) + "_l" + k);
                            isOnly.add(isOnly.get(zs));
                        }
                        lastCellNum1 = lastCellNum1 - mergedCols2 + 1;
                        t = j + mergedCols2;
                    }else {
                        XSSFRow row1 = sheet.getRow(res_h);
                        XSSFCell cell = row1.getCell(p);
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);
                        if(!StringUtils.isEmpty(stringCellValue)){
                            temp.add(stringCellValue);
                            Boolean onlyXlsx = isOnlyXlsx(cell);
                            isOnly.add(onlyXlsx);
                        }
                        CellRangeAddress mergedRegion1 = getMergedRegion(sheet, res_h, p);
                        int mergedCols2 = mergedRegion1.getLastColumn() - mergedRegion1.getFirstColumn() + 1;
                        int cs = mergedCols2 * hbhNum;
                        int zs = temp.size()-1;
                        for (int k = 1; k < cs; k++) {
                            temp.add(temp.get(zs) + "_l" + k);
                            isOnly.add(isOnly.get(zs));
                        }
                        lastCellNum1 = lastCellNum1 - mergedCols2 + 1;
                        t = t + mergedCols2 - 1;
                    }
                }
            }
            //取出每一行，并重新赋值为首拼音
            for (String s : temp) {
                String firstLetters = PinyinUtils.getFirstLetters(s);
                // 对命名做规范处理
                String name = PinyinUtils.sanitizeTableName(firstLetters);
                res_list.add(name);
            }

            Map<String,String> map1 = new HashMap<>();
            List<TableColumn> tableColumns = new ArrayList<>();
            List<String> res_name = new ArrayList<>();
            // 由于代码太多，改起来太麻烦，现在将添加注释的所有注释另外返回--此处再修改返回类型改起来麻烦，直接采用本地redis存取-换成变量方式，不采用三方存储
            for (int j = 0; j < temp.size(); j++) {
                String s = temp.get(j);
                String firstLetters = PinyinUtils.getFirstLetters(s);
                // 对命名做规范处理
                String name = PinyinUtils.sanitizeTableName(firstLetters);
                res_name.add(name);
            }
            /**
             * 处理完后对重名做处理
             * 例如：ql:清理；ql:权利，这样key相同数据会覆盖
             * */
            for (int j = 0; j < res_name.size(); j++) {
                for (int k = j+1; k < res_name.size(); k++) {
                    // 依次与后面的数据比较，若相同则添加对应序列
                    if(res_name.get(j).equals(res_name.get(k))){
                        String s = res_name.get(j);
                        res_name.remove(k);
                        res_name.add(k,s+"_"+k);
                    }
                }
            }

            for (int j = 0; j < temp.size(); j++) {
                map1.put(res_name.get(j),temp.get(j));
                TableColumn tableColumn = new TableColumn();
                tableColumn.setComment(temp.get(j));
                tableColumn.setOnly(isOnly.get(j));
                tableColumns.add(tableColumn);
            }

            Map<Integer,Map<String,String>> mapH = new HashMap<>();
            mapH.put(res_h,map1);
            stMap.put(i,mapH);
            staticMap.put(i,tableColumns);

            map.put(String.valueOf(res_h),res_list);
            mapMap.put(i,map);
        }

        return mapMap;
    }

    private static int getLhXlsx(XSSFSheet sheet, Integer res_h) {
        //取第一行数据最多合并行
        XSSFRow row = sheet.getRow(res_h + 1);
        int lastCellNum2 = row.getLastCellNum();
        //最多合并的行数
        int lh = 0;
        for (int j = 0; j < lastCellNum2; j++) {
            CellRangeAddress mergedRegion = getMergedRegion(sheet, res_h + 1, j);
            if(mergedRegion != null){
                //合并行数
                int mergedRows = mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1;
                if(lh < mergedRows){
                    lh = mergedRows;
                }
            }
        }
        return lh;
    }

    private static int getLhXls(HSSFSheet sheet, Integer res_h) {
        //取第一行数据最多合并行
        HSSFRow row = sheet.getRow(res_h + 1);
        int lastCellNum2 = row.getLastCellNum();
        //最多合并的行数
        int lh = 0;
        for (int j = 0; j < lastCellNum2; j++) {
            CellRangeAddress mergedRegion = getMergedRegion(sheet, res_h + 1, j);
            if(mergedRegion != null){
                //合并行数
                int mergedRows = mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1;
                if(lh < mergedRows){
                    lh = mergedRows;
                }
            }
        }
        return lh;
    }

    private static int getLLXlsx(XSSFSheet sheet, Integer res_h) {
        int lh = 0;
        XSSFRow row = sheet.getRow(res_h);
        int lastCellNum2 = row.getLastCellNum();
        for (int j = 0; j < lastCellNum2; j++) {
            CellRangeAddress mergedRegion = getMergedRegion(sheet, res_h, j);
            if(mergedRegion != null){
                //合并列数
                int mergedRows = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;
                lh = lh + mergedRows;
                j = j + mergedRows - 1;
            }
        }
        return lh;
    }

    private static int getLLXls(HSSFSheet sheet, Integer res_h) {
        int lh = 0;
        HSSFRow row = sheet.getRow(res_h);
        int lastCellNum2 = row.getLastCellNum();
        for (int j = 0; j < lastCellNum2; j++) {
            CellRangeAddress mergedRegion = getMergedRegion(sheet, res_h, j);
            if(mergedRegion != null){
                //合并列数
                int mergedRows = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;
                lh = lh + mergedRows;
                j = j + mergedRows - 1;
            }
        }
        return lh;
    }

    /**
     * 设置字段名
     * 返回为：第一层，第几张表，第二层，第几行的数据确定的表字段名
     * */
    public static Map<Integer,Map<String, List<String>>> setValuesXLS(HSSFWorkbook excel) {
        Map<Integer,Map<String, List<String>>> mapMap = new HashMap<>();
        int sheets = excel.getNumberOfSheets();

        for (int i = 0; i < sheets; i++) {
            HSSFSheet sheet = excel.getSheetAt(i);
            // 字段名集合
            List<String> res_list = new ArrayList<>();
            // 最终取的行
            Integer res_h = 0;
            Map<String,List<String>> map = new HashMap<>();

            //行数
            int lastRowNum = sheet.getLastRowNum() + 1;

            List<String> temp = List.of();
            //是否具有唯一性
            List<Boolean> isOnly = List.of();
            //以第一次出现的最长不为空的行为基础创建表
            int listLength = 0;
            int lastLength = 0;
            for (int j = 0; j < lastRowNum; j++) {
                List<String> list = new ArrayList<>();
                List<Boolean> isOnlyIn = new ArrayList<>();

                HSSFRow row1 = sheet.getRow(j);
                if(row1 != null){
                    // 该行列数
                    int lastCellNum = row1.getLastCellNum();
                    for (int k = 0; k < lastCellNum; k++) {
                        boolean mergedCell = isMergedCell(sheet, j, k);
                        if(mergedCell){
                            continue;
                        }
                        HSSFCell cell = row1.getCell(k);
                        // 保持原始内容
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);
                        if(!StringUtils.isEmpty(stringCellValue)){
                            list.add(stringCellValue);
                            Boolean onlyXls = isOnlyXls(cell);
                            isOnlyIn.add(onlyXls);
                        }
                    }
                    listLength = list.size();
                }
                if(row1 != null) {
                    int lastCellNum = row1.getLastCellNum();
                    for (int k = 0; k < lastCellNum; k++) {
                        //计算该列是否是合并列
                        boolean mergedCell = isMergedCell(sheet, j, k);

                        HSSFRow row = sheet.getRow(j);
                        HSSFCell cell = row.getCell(k);
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);
                        if (StringUtils.isEmpty(stringCellValue)) {
                            //为空数据，则不加列数-包括合并数据，合并表格只要有数据，都会添加对应的列数（此处是为防止单元格后面加空数据影响判断数据）
                            continue;
                        }
                        if (mergedCell) {
                            //是合并列，则添加列数
                            CellRangeAddress mergedRegion = getMergedRegion(sheet, j, k);
                            int mergedCols = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;
                            if (mergedCols != 1) {
                                listLength = listLength + mergedCols;
                                k = k + mergedCols - 1;
                            }else {
                                //属于合并行，则只加1
                                listLength = listLength + 1;
                            }
                        }
                    }
                }

                // 比较该行和上一行的长度大小
                if(j != 0){ //从第二行开始
                    if(lastLength < listLength){
                        temp = list;
                        // 发生变化时，变成对应的行
                        res_h = j;
                        lastLength = listLength;
                        isOnly = isOnlyIn;
                    }
                }else {
                    // 存储为已知最长的一行的信息
                    temp = list;
                    lastLength = listLength;
                    res_h = 0; //初始为第一行
                    isOnly = isOnlyIn;
                }

            }
            HSSFRow row = sheet.getRow(res_h);
            if(row == null){
                //证明是空的表格
                continue;
            }
            int lastCellNum = row.getLastCellNum();
            //计算合并列数
            int mergedCols1 = 0;
            for (int j = 0; j < lastCellNum; j++) {
                boolean mergedCell = isMergedCell(sheet, res_h, j);
                if(mergedCell) {
                    CellRangeAddress mergedRegion = getMergedRegion(sheet, res_h, j);
                    int mergedCols = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;
                    mergedCols1 = mergedCols1 + mergedCols - 1;
                    addRowNum = addRowNum + mergedCols - 1;
                    j = j + mergedCols - 1;
                    hblNum = hblNum + 1;
                }
            }

            //数据行第一行最多合并行的列确定合并行数
            int lh = getLhXls(sheet, res_h);

            int mergedRows = 0;
            if(lh > 0){
                System.out.println("有合并行数据");
                // 计算合并的行数和列数
                mergedRows = lh;

                hbhNum = mergedRows;
                short lastCellNum1 = row.getLastCellNum();
                int m = 0;
                for (int j = 0; j < lastCellNum1; j++) {
                    //先判断是否是合并列下的数据，合并列则跳过不在此处理
                    boolean mergedCell1 = isMergedCell(sheet, res_h, j);
                    if(mergedCell1){
                        continue;
                    }
                    //有合并行加列数据
                    for (int k = 0; k < mergedRows; k++) {
                        boolean mergedCell = isMergedCell(sheet, res_h + k + 1, j);
                        if(!mergedCell){
                            //合并行后出现的未合并行数据
                            if(k > 0){
                                //从第二个开始增加列数据
                                //在最后加一列数据
                                temp.add(temp.get(m)+"_n"+k);
                                //字段是否具有唯一性也同样加一个，是合并行则类型实际上一样的唯一性
                                isOnly.add(isOnly.get(m));
                            }
                        }
                    }
                    m = m + 1;
                }
                for (int j = 0; j < lastCellNum1; j++) {
                    for (int k = 0; k < mergedRows; k++) {
                        boolean mergedCell = isMergedCell(sheet, res_h + k + 1, j);
                        if(!mergedCell){
                            //合并行后出现的未合并行数据
                            if(k > 0){
                                //从第二个开始增加列数据
                                //合并行列数加一
                                addCelNum = addCelNum + 1;
                            }
                        }
                    }
                }
            }

            int lastCellNum1 = row.getLastCellNum();
            int t = 0;
            int p = 0;
            //合并列加数据
            for (int j = 0; j < lastCellNum1; j++) {
                boolean mergedCell;
                if(t == 0) {
                    mergedCell = isMergedCell(sheet, res_h, j);
                }else {
                    mergedCell = isMergedCell(sheet, res_h, t);
                    p = t;
                    t = t + 1;
                }
                if(mergedCell){
                    if(t == 0) {
                        HSSFRow row1 = sheet.getRow(res_h);
                        HSSFCell cell = row1.getCell(j);
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);
                        if(!StringUtils.isEmpty(stringCellValue)){
                            temp.add(stringCellValue);
                            Boolean onlyXlsx = isOnlyXls(cell);
                            isOnly.add(onlyXlsx);
                        }
                        CellRangeAddress mergedRegion1 = getMergedRegion(sheet, res_h, j);
                        int mergedCols2 = mergedRegion1.getLastColumn() - mergedRegion1.getFirstColumn() + 1;
                        int lhXls = getLhXls(sheet, res_h);
                        int cs;
                        if(lhXls == 0){
                            cs = mergedCols2;
                        }else {
                            cs = mergedCols2 * lhXls;
                        }

                        int zs = temp.size()-1;
                        for (int k = 1; k < cs; k++) {
                            temp.add(temp.get(zs) + "_l" + k);
                            isOnly.add(isOnly.get(zs));
                        }
                        lastCellNum1 = lastCellNum1 - mergedCols2 + 1;
                        t = j + mergedCols2;
                    }else {
                        HSSFRow row1 = sheet.getRow(res_h);
                        HSSFCell cell = row1.getCell(p);
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);
                        if(!StringUtils.isEmpty(stringCellValue)){
                            temp.add(stringCellValue);
                            Boolean onlyXlsx = isOnlyXls(cell);
                            isOnly.add(onlyXlsx);
                        }
                        CellRangeAddress mergedRegion1 = getMergedRegion(sheet, res_h, p);
                        int mergedCols2 = mergedRegion1.getLastColumn() - mergedRegion1.getFirstColumn() + 1;
                        int cs = mergedCols2 * hbhNum;
                        int zs = temp.size()-1;
                        for (int k = 1; k < cs; k++) {
                            temp.add(temp.get(zs) + "_l" + k);
                            isOnly.add(isOnly.get(zs));
                        }
                        lastCellNum1 = lastCellNum1 - mergedCols2 + 1;
                        t = t + mergedCols2 - 1;
                    }
                }
            }
            //取出每一行，并重新赋值为首拼音
            for (String s : temp) {
                String firstLetters = PinyinUtils.getFirstLetters(s);
                // 对命名做规范处理
                String name = PinyinUtils.sanitizeTableName(firstLetters);
                res_list.add(name);
            }

            Map<String,String> map1 = new HashMap<>();
            List<TableColumn> tableColumns = new ArrayList<>();
            List<String> res_name = new ArrayList<>();
            // 由于代码太多，改起来太麻烦，现在将添加注释的所有注释另外返回--此处再修改返回类型改起来麻烦，直接采用本地redis存取-换成变量方式，不采用三方存储
            for (int j = 0; j < temp.size(); j++) {
                String s = temp.get(j);
                String firstLetters = PinyinUtils.getFirstLetters(s);
                // 对命名做规范处理
                String name = PinyinUtils.sanitizeTableName(firstLetters);
                res_name.add(name);
            }
            /**
             * 处理完后对重名做处理
             * 例如：ql:清理；ql:权利，这样key相同数据会覆盖
             * */
            for (int j = 0; j < res_name.size(); j++) {
                for (int k = j+1; k < res_name.size(); k++) {
                    // 依次与后面的数据比较，若相同则添加对应序列
                    if(res_name.get(j).equals(res_name.get(k))){
                        String s = res_name.get(j);
                        res_name.remove(k);
                        res_name.add(k,s+"_"+k);
                    }
                }
            }

            for (int j = 0; j < temp.size(); j++) {
                map1.put(res_name.get(j),temp.get(j));
                TableColumn tableColumn = new TableColumn();
                tableColumn.setComment(temp.get(j));
                tableColumn.setOnly(isOnly.get(j));
                tableColumns.add(tableColumn);
            }

            Map<Integer,Map<String,String>> mapH = new HashMap<>();
            mapH.put(res_h,map1);
            stMap.put(i,mapH);
            staticMap.put(i,tableColumns);

            map.put(String.valueOf(res_h),res_list);
            mapMap.put(i,map);
        }

        return mapMap;
    }

    /**
     * @description: TODO 获取该excel表格最长不为空的一行
     * @author zb
     * @date 2025/8/18
     * @version: 1.0
     * @return
    */
    public static List<TableMsg> getLong(MultipartFile file,Integer sl,Boolean isc) throws IOException {
        String name = file.getOriginalFilename();
        // 文件后缀
        String la = name.substring(name.lastIndexOf(".") + 1);
        if (la.equals("xlsx")) {
            XSSFWorkbook excel = new XSSFWorkbook(file.getInputStream());
            int sheets = excel.getNumberOfSheets();
            List<TableMsg> tableMsgs = new ArrayList<>();
            for (int t = 0; t < sheets; t++) {
                XSSFSheet sheet = excel.getSheetAt(t);
                int lastRowNum = sheet.getLastRowNum();
                //最终取的行
                Integer h = null;
                //最终取的行的列数
                Integer lNum = 0;
                if(sl == null || sl < 0) {
                    for (int i = 0; i <= lastRowNum; i++) {
                        //记录列数
                        int ln = 0;
                        XSSFRow row = sheet.getRow(i);
                        int lastCellNum = row.getLastCellNum();
                        for (int j = 0; j < lastCellNum; j++) {
                            XSSFCell cell = row.getCell(j);
                            if (cell != null) {
                                DataFormatter formatter = new DataFormatter();
                                String stringCellValue = formatter.formatCellValue(cell);
                                if (StringUtils.isNotEmpty(stringCellValue)) {
                                    ln++;
                                }
                            }
                        }
                        if (i == 0) {
                            //初始值
                            h = 0;
                            lNum = ln;
                        } else {
                            if (lNum < ln) {
                                lNum = ln;
                                h = i;
                            }
                        }
                    }
                }else {
                    h = sl;
                    XSSFRow row = sheet.getRow(h);
                    lNum = Integer.valueOf(row.getLastCellNum());
                }
                if(h == null){
                    //空表
                    continue;
                }
                XSSFRow row = sheet.getRow(h);
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i < lastCellNum; i++) {
                    XSSFCell cell = row.getCell(i);
                    Boolean compareXlsx = true;
                    if (cell != null && isc) {
                        compareXlsx = isCompareXlsx(cell);
                    }
                    if (compareXlsx) {
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);

                        TableMsg tableMsg = new TableMsg();
                        tableMsg.setBNo(t);
                        tableMsg.setHNo(h);
                        tableMsg.setLNo(i);
                        tableMsg.setCellName(PinyinUtils.sanitizeTableName(PinyinUtils.getFirstLetters(stringCellValue)));
                        tableMsg.setCellYName(stringCellValue);
                        List<Object> list = new ArrayList<>();
                        for (int j = h + 1; j <= lastRowNum; j++) {
                            XSSFRow row1 = sheet.getRow(j);
                            XSSFCell cell1 = row1.getCell(i);
                            String stringCellValue1 = formatter.formatCellValue(cell1);
                            list.add(stringCellValue1);
                        }
                        tableMsg.setList(list);
                        tableMsgs.add(tableMsg);
                    }
                }
            }
            return tableMsgs;
        } else if (la.equals("xls")) {
            HSSFWorkbook excel = new HSSFWorkbook(file.getInputStream());
            int sheets = excel.getNumberOfSheets();
            List<TableMsg> tableMsgs = new ArrayList<>();
            for (int t = 0; t < sheets; t++) {
                HSSFSheet sheet = excel.getSheetAt(t);
                int lastRowNum = sheet.getLastRowNum();
                //最终取的行
                Integer h = null;
                //最终取的行的列数
                Integer lNum = 0;
                for (int i = 0; i <= lastRowNum; i++) {
                    //记录列数
                    int ln = 0;
                    HSSFRow row = sheet.getRow(i);
                    int lastCellNum = row.getLastCellNum();
                    for (int j = 0; j < lastCellNum; j++) {
                        HSSFCell cell = row.getCell(j);
                        if (cell != null) {
                            DataFormatter formatter = new DataFormatter();
                            String stringCellValue = formatter.formatCellValue(cell);
                            if (StringUtils.isNotEmpty(stringCellValue)) {
                                ln++;
                            }
                        }
                    }
                    if (i == 0) {
                        //初始值
                        h = 0;
                        lNum = ln;
                    } else {
                        if (lNum < ln) {
                            lNum = ln;
                            h = i;
                        }
                    }
                }
                HSSFRow row = sheet.getRow(h);
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i < lastCellNum; i++) {
                    HSSFCell cell = row.getCell(i);
                    Boolean compareXlsx = false;
                    if (cell != null) {
                        compareXlsx = isCompareXls(cell);
                    }
                    if (compareXlsx) {
                        DataFormatter formatter = new DataFormatter();
                        String stringCellValue = formatter.formatCellValue(cell);

                        TableMsg tableMsg = new TableMsg();
                        tableMsg.setBNo(t);
                        tableMsg.setHNo(h);
                        tableMsg.setLNo(i);
                        tableMsg.setCellName(PinyinUtils.sanitizeTableName(PinyinUtils.getFirstLetters(stringCellValue)));
                        tableMsg.setCellYName(stringCellValue);
                        List<Object> list = new ArrayList<>();
                        for (int j = h + 1; j <= lastRowNum; j++) {
                            HSSFRow row1 = sheet.getRow(j);
                            HSSFCell cell1 = row1.getCell(i);
                            String stringCellValue1 = formatter.formatCellValue(cell1);
                            list.add(stringCellValue1);
                        }
                        tableMsg.setList(list);
                        tableMsgs.add(tableMsg);
                    }
                }
            }
            return tableMsgs;
        }
        return null;
    }
}
